-- ============================================
-- Payment Recovery System - Portal Users RESET Queries
-- Created: December 19, 2025
-- For: Hostgator Production Import (Step 2)
-- ============================================
-- 
-- PURPOSE: RESET portal_users to simulate "before payment" state for testing recovery emails
-- IMPORT ORDER: Run this AFTER importing payment_recovery_tables_no_fk.sql
-- 
-- This file resets portal_users fields based on captured test payments:
-- - UID Registration: Sets paid=0, clears validity dates (keeps UID)
-- - Membership Renewal: Sets paid=0, expired=1, restores old expiry_date from profile
-- - Certificate Payment: Sets it_belt_payment=NULL
-- 
-- Date Range: 2025-10-11 00:00:00 to 2025-12-13 11:04:00
-- Payment Mode: test (only affects test mode payments)
-- 
-- WARNING: These are RESET queries for testing recovery emails!
-- Run on local/staging environment, NOT on live production database!
-- ============================================

START TRANSACTION;

-- -----------------------------------------------
-- RESET 1: Black Belt Certificate Payments (Independent)
-- -----------------------------------------------
-- Resets certificate payment status to simulate unpaid state
-- - Sets it_belt_payment = NULL
-- This runs first and is independent of other resets

UPDATE portal_users u
JOIN payments p ON u.id = p.user_id
SET u.it_belt_payment = NULL
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.purpose IN ('Black Belt Certificate', 'Certificate')
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00';

-- Verify Certificate Payment resets
SELECT 
    'Certificate Payment Reset' as reset_type,
    COUNT(*) as users_reset,
    SUM(CASE WHEN u.it_belt_payment IS NULL THEN 1 ELSE 0 END) as nulled_count
FROM portal_users u
JOIN payments p ON u.id = p.user_id
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.purpose IN ('Black Belt Certificate', 'Certificate')
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00';

-- -----------------------------------------------
-- RESET 2: Membership Renewal Payments (Priority over UID)
-- -----------------------------------------------
-- Resets membership renewal to simulate expired state
-- - Sets paid = 0, expired = 1
-- - Clears valid_from, valid_up_to
-- - Restores old expiry_date from profile table
-- This has priority over UID registration reset

UPDATE portal_users u
JOIN payments p ON u.id = p.user_id
LEFT JOIN profile pr ON u.reg_uid = pr.reg_id
SET 
    u.paid = 0,
    u.expired = 1,
    u.valid_from = NULL,
    u.valid_up_to = NULL,
    u.expiry_date = COALESCE(pr.expired_date, u.expiry_date)
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.purpose = 'Membership Renewal'
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00'
AND u.reg_uid IS NOT NULL;

-- Verify Membership Renewal resets
SELECT 
    'Membership Renewal Reset' as reset_type,
    COUNT(*) as users_reset,
    SUM(CASE WHEN u.paid = 0 THEN 1 ELSE 0 END) as unpaid_count,
    SUM(CASE WHEN u.expired = 1 THEN 1 ELSE 0 END) as expired_count,
    MIN(u.expiry_date) as earliest_old_expiry,
    MAX(u.expiry_date) as latest_old_expiry
FROM portal_users u
JOIN payments p ON u.id = p.user_id
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.purpose = 'Membership Renewal'
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00';

-- -----------------------------------------------
-- RESET 3: UID Registration Payments (Only if NO Membership Renewal)
-- -----------------------------------------------
-- Resets UID registration to simulate unpaid state
-- - Sets paid = 0
-- - Clears expiry_date, valid_from, valid_up_to
-- - Keeps reg_uid (user keeps existing UID from previous attempt)
-- Only runs for users who DON'T have Membership Renewal payments (priority conflict)

UPDATE portal_users u
JOIN payments p ON u.id = p.user_id
SET 
    u.paid = 0,
    u.expiry_date = NULL,
    u.valid_from = NULL,
    u.valid_up_to = NULL
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.purpose = 'UID Registration'
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00'
-- Exclude users who have Membership Renewal payments (priority conflict resolution)
AND NOT EXISTS (
    SELECT 1 FROM payments p2
    WHERE p2.user_id = u.id
    AND p2.status = 'captured'
    AND p2.payment_mode = 'test'
    AND p2.purpose = 'Membership Renewal'
    AND p2.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00'
);

-- Verify UID Registration resets
SELECT 
    'UID Registration Reset' as reset_type,
    COUNT(*) as users_reset,
    SUM(CASE WHEN u.paid = 0 THEN 1 ELSE 0 END) as unpaid_count,
    SUM(CASE WHEN u.expiry_date IS NULL THEN 1 ELSE 0 END) as null_expiry_count,
    COUNT(DISTINCT u.reg_uid) as uid_count_preserved
FROM portal_users u
JOIN payments p ON u.id = p.user_id
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.purpose = 'UID Registration'
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00'
AND NOT EXISTS (
    SELECT 1 FROM payments p2
    WHERE p2.user_id = u.id
    AND p2.status = 'captured'
    AND p2.payment_mode = 'test'
    AND p2.purpose = 'Membership Renewal'
    AND p2.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00'
);

COMMIT;

-- -----------------------------------------------
-- OVERALL RESET SUMMARY
-- -----------------------------------------------
-- Shows all reset users and their current state after reset

SELECT 
    'OVERALL RESET SUMMARY' as report_type,
    COUNT(DISTINCT u.id) as total_users_reset,
    SUM(CASE WHEN p.purpose = 'UID Registration' THEN 1 ELSE 0 END) as uid_registrations,
    SUM(CASE WHEN p.purpose = 'Membership Renewal' THEN 1 ELSE 0 END) as membership_renewals,
    SUM(CASE WHEN p.purpose IN ('Black Belt Certificate', 'Certificate') THEN 1 ELSE 0 END) as certificates,
    SUM(CASE WHEN u.paid = 0 THEN 1 ELSE 0 END) as users_now_unpaid,
    SUM(CASE WHEN u.expired = 1 THEN 1 ELSE 0 END) as users_now_expired,
    SUM(CASE WHEN u.it_belt_payment IS NULL THEN 1 ELSE 0 END) as certificates_now_unpaid
FROM portal_users u
JOIN payments p ON u.id = p.user_id
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00';

-- -----------------------------------------------
-- DETAILED RESET USER LIST
-- -----------------------------------------------
-- Shows each reset user with their current state after reset

SELECT 
    u.id,
    u.email,
    u.fname,
    u.lname,
    u.reg_uid,
    u.paid as current_paid_status,
    u.expired as current_expired_status,
    u.it_belt_payment as current_cert_status,
    u.valid_from,
    u.valid_up_to,
    u.expiry_date as current_expiry_date,
    p.id as payment_id,
    p.payment_id as razorpay_id,
    p.purpose,
    p.amount,
    p.created_at as payment_date,
    CASE 
        WHEN p.purpose = 'UID Registration' AND u.paid = 0 THEN 'Reset: Unpaid'
        WHEN p.purpose = 'Membership Renewal' AND u.expired = 1 THEN 'Reset: Expired'
        WHEN p.purpose IN ('Black Belt Certificate', 'Certificate') AND u.it_belt_payment IS NULL THEN 'Reset: Cert Unpaid'
        ELSE 'Not Reset (conflict or error)'
    END as reset_status
FROM portal_users u
JOIN payments p ON u.id = p.user_id
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00'
ORDER BY p.created_at DESC, p.purpose;

-- -----------------------------------------------
-- MULTI-PURPOSE PAYMENT USERS
-- -----------------------------------------------
-- Identify users with multiple payment purposes (shows conflict resolution)

SELECT 
    u.id,
    u.email,
    u.reg_uid,
    GROUP_CONCAT(DISTINCT p.purpose ORDER BY p.created_at SEPARATOR ' + ') as multiple_purposes,
    COUNT(DISTINCT p.purpose) as purpose_count,
    u.paid,
    u.expired,
    u.it_belt_payment,
    CASE 
        WHEN u.expired = 1 THEN 'Membership Renewal Priority Applied'
        WHEN u.paid = 0 AND u.expiry_date IS NULL THEN 'UID Registration Reset Applied'
        ELSE 'Check Status'
    END as priority_logic
FROM portal_users u
JOIN payments p ON u.id = p.user_id
WHERE p.status = 'captured'
AND p.payment_mode = 'test'
AND p.created_at BETWEEN '2025-10-11 00:00:00' AND '2025-12-13 11:04:00'
GROUP BY u.id
HAVING purpose_count > 1
ORDER BY purpose_count DESC, u.id;

-- ============================================
-- IMPORTANT NOTES
-- ============================================

-- 1. PURPOSE OF THIS FILE:
--    These are RESET queries to simulate "before payment" state
--    Used for testing recovery email system on local/staging environment
--    DO NOT run on live production database!

-- 2. IMPORT ORDER:
--    Step 1: Import payment_recovery_tables_no_fk.sql (creates and populates recovery tables)
--    Step 2: Import THIS file (resets portal_users to unpaid state)
--    Step 3: Test recovery email system

-- 3. DATE RANGE:
--    Currently set to: 2025-10-11 00:00:00 to 2025-12-13 11:04:00
--    Modify these dates in all queries if needed

-- 4. PAYMENT MODE:
--    Only affects: payment_mode = 'test'
--    Safe to run as it only touches test payments
--    Live payments are NOT affected

-- 5. BACKUP FIRST:
--    Always backup portal_users table before running UPDATE queries
--    mysqldump -u username -p database_name portal_users > portal_users_backup.sql

-- 6. RESET LOGIC BY PURPOSE:
--    
--    Black Belt Certificate:
--    - it_belt_payment = NULL (reset to unpaid)
--    - Independent, always runs
--    
--    Membership Renewal (Old Users):
--    - paid = 0, expired = 1
--    - valid_from = NULL, valid_up_to = NULL
--    - expiry_date = <old value from profile.expired_date table>
--    - Priority over UID Registration
--    
--    UID Registration:
--    - paid = 0
--    - expiry_date = NULL, valid_from = NULL, valid_up_to = NULL
--    - Keeps reg_uid (preserves existing UID)
--    - Only runs if NO Membership Renewal payment exists

-- 7. MULTI-PURPOSE PAYMENT HANDLING:
--    If user has multiple payment purposes:
--    - Certificate reset always runs (independent)
--    - Membership Renewal takes priority over UID Registration
--    - UID Registration only resets if no Renewal payment exists
--    This prevents conflicting resets

-- 8. PROFILE TABLE INTEGRATION:
--    For Membership Renewal, fetches old expired_date:
--    - Joins profile table: portal_users.reg_uid = profile.reg_id
--    - Restores original expired date before renewal
--    - Uses COALESCE fallback if profile entry not found

-- 9. TRANSACTION SAFETY:
--    All resets wrapped in single TRANSACTION
--    If any UPDATE fails, entire reset rolls back
--    Ensures atomic operation (all or nothing)

-- 10. VERIFICATION QUERIES:
--     Each UPDATE section includes verification queries
--     Run these to confirm resets were applied correctly
--     Check multi-purpose users for proper priority handling

-- 11. TESTING RECOVERY EMAILS:
--     After running this reset:
--     1. Recovery tables have records (status = 'pending')
--     2. Portal_users show unpaid/expired state
--     3. Recovery email system can now test sending emails
--     4. When users click email links and pay, RazorpayPaymentController updates portal_users
