-- Payment Security & Recovery System - SQL Verification Script
-- Run these queries to verify system integrity

-- ================================================================
-- SECTION 1: IDENTIFY STUCK USERS (Need Recovery)
-- ================================================================

SELECT 
    '=== USERS REQUIRING RECOVERY ===' as Section;

SELECT 
    p.id as payment_db_id,
    p.payment_id as razorpay_payment_id,
    p.user_id,
    u.name,
    u.email,
    u.mobile,
    u.reg_uid,
    u.paid,
    p.amount / 100 as amount_inr,
    p.status as payment_status,
    p.created_at as payment_date,
    DATEDIFF(NOW(), p.created_at) as days_pending,
    CASE 
        WHEN u.reg_uid IS NULL OR u.reg_uid = '' THEN 'NO_UID'
        WHEN u.paid = 0 THEN 'HAS_UID_NOT_PAID'
        ELSE 'UNKNOWN'
    END as issue_type
FROM payments p
JOIN portal_users u ON p.user_id = u.id
WHERE p.status = 'captured' 
AND p.purpose = 'UID Registration'
AND (u.reg_uid IS NULL OR u.reg_uid = '' OR u.paid = 0)
ORDER BY p.created_at ASC;

-- Get count and total amount
SELECT 
    COUNT(*) as stuck_user_count,
    SUM(p.amount) / 100 as total_pending_inr,
    MIN(p.created_at) as oldest_payment,
    MAX(p.created_at) as newest_payment
FROM payments p
JOIN portal_users u ON p.user_id = u.id
WHERE p.status = 'captured' 
AND p.purpose = 'UID Registration'
AND (u.reg_uid IS NULL OR u.reg_uid = '' OR u.paid = 0);

-- ================================================================
-- SECTION 2: DATA INTEGRITY CHECKS (Should All Return 0)
-- ================================================================

SELECT 
    '=== DATA INTEGRITY CHECKS ===' as Section;

-- Check 1: Users with paid=1 but no UID (INVALID)
SELECT 
    'Users with paid=1 but no UID' as check_name,
    COUNT(*) as violations
FROM portal_users 
WHERE paid = 1 
AND (reg_uid IS NULL OR reg_uid = '');

-- Check 2: Users with UID but paid=0 (INVALID)
SELECT 
    'Users with UID but paid=0' as check_name,
    COUNT(*) as violations
FROM portal_users 
WHERE paid = 0 
AND reg_uid IS NOT NULL 
AND reg_uid != '';

-- Check 3: Duplicate payment_ids (INVALID)
SELECT 
    'Duplicate payment_ids' as check_name,
    COUNT(*) as violations
FROM (
    SELECT payment_id, COUNT(*) as cnt
    FROM payments
    WHERE payment_id IS NOT NULL
    GROUP BY payment_id
    HAVING cnt > 1
) as dupes;

-- Check 4: Duplicate reg_uids (INVALID)
SELECT 
    'Duplicate reg_uids' as check_name,
    COUNT(*) as violations
FROM (
    SELECT reg_uid, COUNT(*) as cnt
    FROM portal_users
    WHERE reg_uid IS NOT NULL AND reg_uid != ''
    GROUP BY reg_uid
    HAVING cnt > 1
) as dupes;

-- ================================================================
-- SECTION 3: PAYMENT STATISTICS
-- ================================================================

SELECT 
    '=== PAYMENT STATISTICS ===' as Section;

-- Overall payment stats
SELECT 
    'Total UID Registration Payments' as metric,
    COUNT(*) as count,
    SUM(amount) / 100 as total_inr
FROM payments
WHERE purpose = 'UID Registration';

SELECT 
    'Captured Payments' as metric,
    COUNT(*) as count,
    SUM(amount) / 100 as total_inr
FROM payments
WHERE purpose = 'UID Registration'
AND status = 'captured';

SELECT 
    'Successful Registrations (paid=1 + UID)' as metric,
    COUNT(*) as count
FROM portal_users
WHERE paid = 1 
AND reg_uid IS NOT NULL 
AND reg_uid != '';

-- ================================================================
-- SECTION 4: RECENT ACTIVITY
-- ================================================================

SELECT 
    '=== RECENT PAYMENTS (Last 24 Hours) ===' as Section;

SELECT 
    p.payment_id,
    u.name,
    u.email,
    u.reg_uid,
    u.paid,
    p.amount / 100 as amount_inr,
    p.status,
    p.created_at,
    CASE 
        WHEN u.paid = 1 AND u.reg_uid IS NOT NULL THEN 'SUCCESS'
        WHEN p.status = 'captured' AND (u.reg_uid IS NULL OR u.paid = 0) THEN 'STUCK'
        ELSE 'PENDING'
    END as flow_status
FROM payments p
JOIN portal_users u ON p.user_id = u.id
WHERE p.purpose = 'UID Registration'
AND p.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY p.created_at DESC
LIMIT 50;

-- ================================================================
-- SECTION 5: WEBHOOK LOG VERIFICATION
-- ================================================================

SELECT 
    '=== USERS WITH PAYMENTS BUT NO UID (Possible Webhook Issues) ===' as Section;

-- Find users who might have webhook captures but no UID
SELECT 
    u.id,
    u.email,
    u.name,
    u.reg_uid,
    u.paid,
    COUNT(p.id) as payment_count,
    GROUP_CONCAT(p.payment_id SEPARATOR ', ') as payment_ids,
    MAX(p.created_at) as latest_payment
FROM portal_users u
JOIN payments p ON u.id = p.user_id
WHERE p.status = 'captured'
AND p.purpose = 'UID Registration'
AND (u.reg_uid IS NULL OR u.reg_uid = '')
GROUP BY u.id
HAVING payment_count >= 1
ORDER BY latest_payment DESC;

-- ================================================================
-- SECTION 6: ADMIN RECOVERY ACTIONS LOG
-- ================================================================

SELECT 
    '=== MANUAL UID GENERATION HISTORY ===' as Section;

-- This would query application logs, but here's the SQL pattern
SELECT 
    u.id,
    u.email,
    u.reg_uid,
    u.paid,
    u.last_updated_by as admin_id,
    u.updated_at as recovery_date
FROM portal_users u
WHERE u.last_updated_by IS NOT NULL
AND u.reg_uid IS NOT NULL
AND u.paid = 1
ORDER BY u.updated_at DESC
LIMIT 20;

-- ================================================================
-- RECOMMENDED ACTIONS
-- ================================================================

SELECT '
========================================
RECOMMENDED ACTIONS BASED ON RESULTS:
========================================

1. If "Users Requiring Recovery" > 0:
   - Go to: Admin Dashboard > User Recovery
   - Review each stuck user
   - Click "Generate UID" for each

2. If Data Integrity Checks show violations:
   - Investigate root cause
   - Run manual cleanup if needed
   - Check application logs

3. If Recent Payments show STUCK status:
   - Immediate action required
   - These are fresh issues

4. Regular Monitoring:
   - Run this script daily
   - Set up automated alerts for stuck users
   - Review webhook logs for anomalies

========================================
' as action_plan;
