-- ============================================================================
-- PRODUCTION-SAFE: PAYMENT RECOVERY BACKFILL (FIXED FOR ACTUAL TABLE STRUCTURE)
-- ============================================================================
-- Purpose: Match recovery records with payments and track conversions
-- Recovery System Start: 2025-12-19 00:00:01
-- Date Created: 2025-12-20
-- 
-- IMPORTANT: Designed for tables WITHOUT amount column in recovery tables
--
-- Usage: Import in phpMyAdmin or run via mysql command line
-- ============================================================================

SELECT '============================================================================' AS info
UNION ALL SELECT 'PAYMENT RECOVERY MATCHING & ANALYTICS'
UNION ALL SELECT '============================================================================'
UNION ALL SELECT 'Recovery Start Date: 2025-12-19 00:00:01'
UNION ALL SELECT 'Matching: User ID + Payment Status (captured) + Purpose + LATEST payment'
UNION ALL SELECT '============================================================================';

-- ============================================================================
-- PHASE 0: ADD AMOUNT COLUMN AND POPULATE FROM PAYMENTS TABLE
-- ============================================================================

SELECT '============================================================================' AS info
UNION ALL SELECT 'PHASE 0: ADDING AMOUNT COLUMN'
UNION ALL SELECT '============================================================================';

-- Add amount column to uid_registration_recoveries if not exists
SET @dbname = DATABASE();
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='amount')>0,'SELECT ''amount column exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `amount` DECIMAL(10,2) NULL AFTER `payment_id`'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add amount column to membership_renewal_recoveries if not exists
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='amount')>0,'SELECT ''amount column exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `amount` DECIMAL(10,2) NULL AFTER `payment_id`'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add amount column to certificate_payment_recoveries if not exists
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='amount')>0,'SELECT ''amount column exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `amount` DECIMAL(10,2) NULL AFTER `payment_id`'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Populate amount from payments table for uid_registration_recoveries
UPDATE uid_registration_recoveries urr
INNER JOIN payments p ON urr.payment_id = p.id
SET urr.amount = COALESCE(p.amount, 1026)
WHERE urr.amount IS NULL;

-- Populate amount from payments table for membership_renewal_recoveries
UPDATE membership_renewal_recoveries mrr
INNER JOIN payments p ON mrr.payment_id = p.id
SET mrr.amount = COALESCE(p.amount, 1026)
WHERE mrr.amount IS NULL;

-- Populate amount from payments table for certificate_payment_recoveries
UPDATE certificate_payment_recoveries cpr
INNER JOIN payments p ON cpr.payment_id = p.id
SET cpr.amount = COALESCE(p.amount, 1026)
WHERE cpr.amount IS NULL;

-- Set default amount for any remaining NULL values
UPDATE uid_registration_recoveries SET amount = 1026 WHERE amount IS NULL;
UPDATE membership_renewal_recoveries SET amount = 1026 WHERE amount IS NULL;
UPDATE certificate_payment_recoveries SET amount = 1026 WHERE amount IS NULL;

SELECT '✅ Amount column added and populated!' AS result;

-- ============================================================================
-- PHASE 0.5: ADD TRACKING COLUMNS
-- ============================================================================

SELECT '>>> Adding tracking columns to uid_registration_recoveries...' AS status;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='new_payment_id')>0,'SELECT ''new_payment_id exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `new_payment_id` BIGINT UNSIGNED NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='payment_completed_at')>0,'SELECT ''payment_completed_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `payment_completed_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='payment_method')>0,'SELECT ''payment_method exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `payment_method` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='days_to_convert')>0,'SELECT ''days_to_convert exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `days_to_convert` INT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='conversion_type')>0,'SELECT ''conversion_type exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `conversion_type` ENUM(''immediate'',''within_24hrs'',''within_week'',''late'',''manual'') NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='recovery_notes')>0,'SELECT ''recovery_notes exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `recovery_notes` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='recovery_channel')>0,'SELECT ''recovery_channel exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `recovery_channel` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='follow_up_count')>0,'SELECT ''follow_up_count exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `follow_up_count` INT DEFAULT 0'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='last_follow_up_at')>0,'SELECT ''last_follow_up_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `last_follow_up_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='is_verified')>0,'SELECT ''is_verified exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `is_verified` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='verified_by')>0,'SELECT ''verified_by exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `verified_by` BIGINT UNSIGNED NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='verified_at')>0,'SELECT ''verified_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `verified_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================================
-- EMAIL TRACKING COLUMNS (Mailgun Webhooks)
-- ============================================================================

SELECT '>>> Adding email tracking columns to uid_registration_recoveries...' AS status;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='mailgun_message_id')>0,'SELECT ''mailgun_message_id exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `mailgun_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================================
-- SMS TRACKING COLUMNS (Gupshup/Twilio/MSG91 Webhooks)
-- ============================================================================

SELECT '>>> Adding SMS tracking columns to uid_registration_recoveries...' AS status;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='sms_sent')>0,'SELECT ''sms_sent exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `sms_sent` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='sms_sent_at')>0,'SELECT ''sms_sent_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `sms_sent_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='sms_status')>0,'SELECT ''sms_status exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `sms_status` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='sms_message_id')>0,'SELECT ''sms_message_id exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `sms_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='sms_provider')>0,'SELECT ''sms_provider exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `sms_provider` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='sms_delivered_at')>0,'SELECT ''sms_delivered_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `sms_delivered_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='sms_failed_reason')>0,'SELECT ''sms_failed_reason exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `sms_failed_reason` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================================
-- WHATSAPP TRACKING COLUMNS (Multi-Provider Webhooks)
-- ============================================================================

SELECT '>>> Adding WhatsApp tracking columns to uid_registration_recoveries...' AS status;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_sent')>0,'SELECT ''whatsapp_sent exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_sent` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_sent_at')>0,'SELECT ''whatsapp_sent_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_sent_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_status')>0,'SELECT ''whatsapp_status exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_status` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_message_id')>0,'SELECT ''whatsapp_message_id exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_provider')>0,'SELECT ''whatsapp_provider exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_provider` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_delivered_at')>0,'SELECT ''whatsapp_delivered_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_delivered_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_read_at')>0,'SELECT ''whatsapp_read_at exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_read_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='uid_registration_recoveries' AND column_name='whatsapp_failed_reason')>0,'SELECT ''whatsapp_failed_reason exists'' AS msg','ALTER TABLE `uid_registration_recoveries` ADD COLUMN `whatsapp_failed_reason` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT '>>> Adding tracking columns to membership_renewal_recoveries...' AS status;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='new_payment_id')>0,'SELECT ''new_payment_id exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `new_payment_id` BIGINT UNSIGNED NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='payment_completed_at')>0,'SELECT ''payment_completed_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `payment_completed_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='payment_method')>0,'SELECT ''payment_method exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `payment_method` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='days_to_convert')>0,'SELECT ''days_to_convert exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `days_to_convert` INT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='conversion_type')>0,'SELECT ''conversion_type exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `conversion_type` ENUM(''immediate'',''within_24hrs'',''within_week'',''late'',''manual'') NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='recovery_notes')>0,'SELECT ''recovery_notes exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `recovery_notes` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='recovery_channel')>0,'SELECT ''recovery_channel exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `recovery_channel` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='follow_up_count')>0,'SELECT ''follow_up_count exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `follow_up_count` INT DEFAULT 0'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='last_follow_up_at')>0,'SELECT ''last_follow_up_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `last_follow_up_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='is_verified')>0,'SELECT ''is_verified exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `is_verified` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='verified_by')>0,'SELECT ''verified_by exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `verified_by` BIGINT UNSIGNED NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='verified_at')>0,'SELECT ''verified_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `verified_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Email tracking for membership renewals
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='mailgun_message_id')>0,'SELECT ''mailgun_message_id exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `mailgun_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- SMS tracking for membership renewals
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='sms_sent')>0,'SELECT ''sms_sent exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `sms_sent` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='sms_sent_at')>0,'SELECT ''sms_sent_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `sms_sent_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='sms_status')>0,'SELECT ''sms_status exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `sms_status` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='sms_message_id')>0,'SELECT ''sms_message_id exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `sms_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='sms_provider')>0,'SELECT ''sms_provider exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `sms_provider` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='sms_delivered_at')>0,'SELECT ''sms_delivered_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `sms_delivered_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='sms_failed_reason')>0,'SELECT ''sms_failed_reason exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `sms_failed_reason` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- WhatsApp tracking for membership renewals
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_sent')>0,'SELECT ''whatsapp_sent exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_sent` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_sent_at')>0,'SELECT ''whatsapp_sent_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_sent_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_status')>0,'SELECT ''whatsapp_status exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_status` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_message_id')>0,'SELECT ''whatsapp_message_id exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_provider')>0,'SELECT ''whatsapp_provider exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_provider` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_delivered_at')>0,'SELECT ''whatsapp_delivered_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_delivered_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_read_at')>0,'SELECT ''whatsapp_read_at exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_read_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='membership_renewal_recoveries' AND column_name='whatsapp_failed_reason')>0,'SELECT ''whatsapp_failed_reason exists'' AS msg','ALTER TABLE `membership_renewal_recoveries` ADD COLUMN `whatsapp_failed_reason` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT '>>> Adding tracking columns to certificate_payment_recoveries...' AS status;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='new_payment_id')>0,'SELECT ''new_payment_id exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `new_payment_id` BIGINT UNSIGNED NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='payment_completed_at')>0,'SELECT ''payment_completed_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `payment_completed_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='payment_method')>0,'SELECT ''payment_method exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `payment_method` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='days_to_convert')>0,'SELECT ''days_to_convert exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `days_to_convert` INT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='conversion_type')>0,'SELECT ''conversion_type exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `conversion_type` ENUM(''immediate'',''within_24hrs'',''within_week'',''late'',''manual'') NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='recovery_notes')>0,'SELECT ''recovery_notes exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `recovery_notes` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='recovery_channel')>0,'SELECT ''recovery_channel exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `recovery_channel` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='follow_up_count')>0,'SELECT ''follow_up_count exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `follow_up_count` INT DEFAULT 0'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='last_follow_up_at')>0,'SELECT ''last_follow_up_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `last_follow_up_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='is_verified')>0,'SELECT ''is_verified exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `is_verified` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='verified_by')>0,'SELECT ''verified_by exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `verified_by` BIGINT UNSIGNED NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='verified_at')>0,'SELECT ''verified_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `verified_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Email tracking for certificate payments
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='mailgun_message_id')>0,'SELECT ''mailgun_message_id exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `mailgun_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- SMS tracking for certificate payments
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='sms_sent')>0,'SELECT ''sms_sent exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `sms_sent` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='sms_sent_at')>0,'SELECT ''sms_sent_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `sms_sent_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='sms_status')>0,'SELECT ''sms_status exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `sms_status` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='sms_message_id')>0,'SELECT ''sms_message_id exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `sms_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='sms_provider')>0,'SELECT ''sms_provider exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `sms_provider` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='sms_delivered_at')>0,'SELECT ''sms_delivered_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `sms_delivered_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='sms_failed_reason')>0,'SELECT ''sms_failed_reason exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `sms_failed_reason` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- WhatsApp tracking for certificate payments
SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_sent')>0,'SELECT ''whatsapp_sent exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_sent` BOOLEAN DEFAULT FALSE'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_sent_at')>0,'SELECT ''whatsapp_sent_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_sent_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_status')>0,'SELECT ''whatsapp_status exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_status` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_message_id')>0,'SELECT ''whatsapp_message_id exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_message_id` VARCHAR(255) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_provider')>0,'SELECT ''whatsapp_provider exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_provider` VARCHAR(50) NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_delivered_at')>0,'SELECT ''whatsapp_delivered_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_delivered_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_read_at')>0,'SELECT ''whatsapp_read_at exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_read_at` TIMESTAMP NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @s = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=@dbname AND table_name='certificate_payment_recoveries' AND column_name='whatsapp_failed_reason')>0,'SELECT ''whatsapp_failed_reason exists'' AS msg','ALTER TABLE `certificate_payment_recoveries` ADD COLUMN `whatsapp_failed_reason` TEXT NULL'));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT '✅ All tracking columns added successfully!' AS result;

-- Check current state
SELECT 
    'Current Recovery Records' AS report,
    COUNT(*) AS uid_registrations
FROM uid_registration_recoveries;

SELECT 
    'Current Recovery Records' AS report,
    COUNT(*) AS membership_renewals
FROM membership_renewal_recoveries;

SELECT 
    'Current Recovery Records' AS report,
    COUNT(*) AS certificate_payments
FROM certificate_payment_recoveries;

-- Check payments since recovery start
SELECT 
    'Payments Since Recovery Started' AS report,
    COUNT(*) AS total_payments,
    SUM(CASE WHEN status = 'captured' THEN 1 ELSE 0 END) AS captured_payments
FROM payments 
WHERE created_at >= '2025-12-19 00:00:01';

SELECT '============================================================================' AS info
UNION ALL SELECT 'PHASE 1: MATCHING UID REGISTRATION RECOVERIES'
UNION ALL SELECT '============================================================================';

-- Match UID Registration Recoveries
UPDATE `uid_registration_recoveries` urr
INNER JOIN (
    SELECT 
        p.id AS payment_id,
        p.user_id,
        p.payment_method,
        p.created_at AS payment_date,
        p.updated_at,
        ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.created_at DESC) AS rn
    FROM `payments` p
    WHERE p.status = 'captured'
        AND p.created_at >= '2025-12-19 00:00:01'
        AND (
            LOWER(COALESCE(p.purpose, '')) LIKE '%uid%' 
            OR LOWER(COALESCE(p.purpose, '')) LIKE '%registration%'
            OR LOWER(COALESCE(p.purpose, '')) LIKE '%reg%'
            OR LOWER(COALESCE(p.description, '')) LIKE '%uid%'
            OR LOWER(COALESCE(p.description, '')) LIKE '%registration%'
        )
) latest_payment ON latest_payment.user_id = urr.user_id AND latest_payment.rn = 1
SET 
    urr.new_payment_id = latest_payment.payment_id,
    urr.payment_completed_at = latest_payment.updated_at,
    urr.payment_method = latest_payment.payment_method,
    urr.status = 'payment_completed',
    urr.days_to_convert = DATEDIFF(latest_payment.payment_date, urr.email_sent_at),
    urr.conversion_type = CASE 
        WHEN TIMESTAMPDIFF(HOUR, urr.email_sent_at, latest_payment.payment_date) <= 1 THEN 'immediate'
        WHEN TIMESTAMPDIFF(HOUR, urr.email_sent_at, latest_payment.payment_date) <= 24 THEN 'within_24hrs'
        WHEN DATEDIFF(latest_payment.payment_date, urr.email_sent_at) <= 7 THEN 'within_week'
        ELSE 'late'
    END,
    urr.recovery_channel = 'email',
    urr.follow_up_count = 1,
    urr.recovery_notes = CONCAT(
        'Auto-matched: Payment ID ', latest_payment.payment_id, 
        ' captured on ', DATE_FORMAT(latest_payment.payment_date, '%Y-%m-%d %H:%i:%s'),
        '. Converted in ', DATEDIFF(latest_payment.payment_date, urr.email_sent_at), ' days.'
    )
WHERE urr.email_sent_at IS NOT NULL
    AND latest_payment.payment_date > urr.email_sent_at
    AND urr.new_payment_id IS NULL;

SELECT 
    '✅ UID Registration Matches' AS result,
    COUNT(*) AS records_matched
FROM uid_registration_recoveries 
WHERE new_payment_id IS NOT NULL;

SELECT '============================================================================' AS info
UNION ALL SELECT 'PHASE 2: MATCHING MEMBERSHIP RENEWAL RECOVERIES'
UNION ALL SELECT '============================================================================';

-- Match Membership Renewal Recoveries
UPDATE `membership_renewal_recoveries` mrr
INNER JOIN (
    SELECT 
        p.id AS payment_id,
        p.user_id,
        p.payment_method,
        p.created_at AS payment_date,
        p.updated_at,
        ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.created_at DESC) AS rn
    FROM `payments` p
    WHERE p.status = 'captured'
        AND p.created_at >= '2025-12-19 00:00:01'
        AND (
            LOWER(COALESCE(p.purpose, '')) LIKE '%membership%' 
            OR LOWER(COALESCE(p.purpose, '')) LIKE '%renewal%'
            OR LOWER(COALESCE(p.purpose, '')) LIKE '%renew%'
            OR LOWER(COALESCE(p.description, '')) LIKE '%membership%'
            OR LOWER(COALESCE(p.description, '')) LIKE '%renewal%'
        )
) latest_payment ON latest_payment.user_id = mrr.user_id AND latest_payment.rn = 1
SET 
    mrr.new_payment_id = latest_payment.payment_id,
    mrr.payment_completed_at = latest_payment.updated_at,
    mrr.payment_method = latest_payment.payment_method,
    mrr.status = 'payment_completed',
    mrr.days_to_convert = DATEDIFF(latest_payment.payment_date, mrr.email_sent_at),
    mrr.conversion_type = CASE 
        WHEN TIMESTAMPDIFF(HOUR, mrr.email_sent_at, latest_payment.payment_date) <= 1 THEN 'immediate'
        WHEN TIMESTAMPDIFF(HOUR, mrr.email_sent_at, latest_payment.payment_date) <= 24 THEN 'within_24hrs'
        WHEN DATEDIFF(latest_payment.payment_date, mrr.email_sent_at) <= 7 THEN 'within_week'
        ELSE 'late'
    END,
    mrr.recovery_channel = 'email',
    mrr.follow_up_count = 1,
    mrr.recovery_notes = CONCAT(
        'Auto-matched: Payment ID ', latest_payment.payment_id, 
        ' captured on ', DATE_FORMAT(latest_payment.payment_date, '%Y-%m-%d %H:%i:%s'),
        '. Converted in ', DATEDIFF(latest_payment.payment_date, mrr.email_sent_at), ' days.'
    )
WHERE mrr.email_sent_at IS NOT NULL
    AND latest_payment.payment_date > mrr.email_sent_at
    AND mrr.new_payment_id IS NULL;

SELECT 
    '✅ Membership Renewal Matches' AS result,
    COUNT(*) AS records_matched
FROM membership_renewal_recoveries 
WHERE new_payment_id IS NOT NULL;

SELECT '============================================================================' AS info
UNION ALL SELECT 'PHASE 3: MATCHING CERTIFICATE PAYMENT RECOVERIES'
UNION ALL SELECT '============================================================================';

-- Match Certificate Payment Recoveries
UPDATE `certificate_payment_recoveries` cpr
INNER JOIN (
    SELECT 
        p.id AS payment_id,
        p.user_id,
        p.payment_method,
        p.created_at AS payment_date,
        p.updated_at,
        ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.created_at DESC) AS rn
    FROM `payments` p
    WHERE p.status = 'captured'
        AND p.created_at >= '2025-12-19 00:00:01'
        AND (
            LOWER(COALESCE(p.purpose, '')) LIKE '%certificate%' 
            OR LOWER(COALESCE(p.purpose, '')) LIKE '%cert%'
            OR LOWER(COALESCE(p.purpose, '')) LIKE '%black%belt%'
            OR LOWER(COALESCE(p.description, '')) LIKE '%certificate%'
            OR LOWER(COALESCE(p.description, '')) LIKE '%cert%'
        )
) latest_payment ON latest_payment.user_id = cpr.user_id AND latest_payment.rn = 1
SET 
    cpr.new_payment_id = latest_payment.payment_id,
    cpr.payment_completed_at = latest_payment.updated_at,
    cpr.payment_method = latest_payment.payment_method,
    cpr.status = 'payment_completed',
    cpr.days_to_convert = DATEDIFF(latest_payment.payment_date, cpr.email_sent_at),
    cpr.conversion_type = CASE 
        WHEN TIMESTAMPDIFF(HOUR, cpr.email_sent_at, latest_payment.payment_date) <= 1 THEN 'immediate'
        WHEN TIMESTAMPDIFF(HOUR, cpr.email_sent_at, latest_payment.payment_date) <= 24 THEN 'within_24hrs'
        WHEN DATEDIFF(latest_payment.payment_date, cpr.email_sent_at) <= 7 THEN 'within_week'
        ELSE 'late'
    END,
    cpr.recovery_channel = 'email',
    cpr.follow_up_count = 1,
    cpr.recovery_notes = CONCAT(
        'Auto-matched: Payment ID ', latest_payment.payment_id, 
        ' captured on ', DATE_FORMAT(latest_payment.payment_date, '%Y-%m-%d %H:%i:%s'),
        '. Converted in ', DATEDIFF(latest_payment.payment_date, cpr.email_sent_at), ' days.'
    )
WHERE cpr.email_sent_at IS NOT NULL
    AND latest_payment.payment_date > cpr.email_sent_at
    AND cpr.new_payment_id IS NULL;

SELECT 
    '✅ Certificate Payment Matches' AS result,
    COUNT(*) AS records_matched
FROM certificate_payment_recoveries 
WHERE new_payment_id IS NOT NULL;

SELECT '============================================================================' AS info
UNION ALL SELECT 'PHASE 4: CREATE MONITORING VIEWS'
UNION ALL SELECT '============================================================================';

-- Dashboard View
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,
    ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2) AS conversion_rate,
    ROUND(AVG(CASE WHEN days_to_convert IS NOT NULL THEN days_to_convert END), 1) AS avg_days_to_convert
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),
    ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2),
    ROUND(AVG(CASE WHEN days_to_convert IS NOT NULL THEN days_to_convert END), 1)
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),
    ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2),
    ROUND(AVG(CASE WHEN days_to_convert IS NOT NULL THEN days_to_convert END), 1)
FROM certificate_payment_recoveries;

-- High Priority Pending View
CREATE OR REPLACE VIEW v_high_priority_pending AS
SELECT 
    'UID Registration' AS type,
    id,
    user_id,
    email_sent_at,
    DATEDIFF(NOW(), email_sent_at) AS days_pending,
    email_open_count,
    email_last_opened_at
FROM uid_registration_recoveries
WHERE new_payment_id IS NULL 
    AND email_sent_at IS NOT NULL
UNION ALL
SELECT 
    'Membership Renewal',
    id,
    user_id,
    email_sent_at,
    DATEDIFF(NOW(), email_sent_at),
    email_open_count,
    email_last_opened_at
FROM membership_renewal_recoveries
WHERE new_payment_id IS NULL 
    AND email_sent_at IS NOT NULL
UNION ALL
SELECT 
    'Certificate Payment',
    id,
    user_id,
    email_sent_at,
    DATEDIFF(NOW(), email_sent_at),
    email_open_count,
    email_last_opened_at
FROM certificate_payment_recoveries
WHERE new_payment_id IS NULL 
    AND email_sent_at IS NOT NULL
ORDER BY days_pending DESC;

SELECT '✅ Views created successfully' AS result;

SELECT '============================================================================' AS info
UNION ALL SELECT 'FINAL RESULTS - DASHBOARD'
UNION ALL SELECT '============================================================================';

-- Show final dashboard
SELECT * FROM v_payment_recovery_dashboard;

SELECT '============================================================================' AS info
UNION ALL SELECT '✅ PAYMENT RECOVERY MATCHING COMPLETED!'
UNION ALL SELECT '============================================================================'
UNION ALL SELECT ''
UNION ALL SELECT 'Next Steps:'
UNION ALL SELECT '1. Review dashboard: SELECT * FROM v_payment_recovery_dashboard;'
UNION ALL SELECT '2. Check pending: SELECT * FROM v_high_priority_pending;'
UNION ALL SELECT '3. Use payment_received filter in admin panel'
UNION ALL SELECT ''
UNION ALL SELECT '============================================================================';

-- END OF SCRIPT
