-- ============================================
-- PAYMENT CORRECTION MODULE SETUP
-- Complete database setup for test payment corrections
-- Date: December 13, 2025
-- Database: final_taekwondo_db
-- ============================================

-- CRITICAL: BACKUP YOUR DATABASE FIRST!
-- In phpMyAdmin: Export > Quick > SQL > Go

-- ============================================
-- OVERVIEW
-- ============================================
-- This script handles the situation where test Razorpay keys
-- were accidentally used in production (Oct 11 - Dec 13, 2025)
-- causing ~560 users to have test payments marked as successful.
-- 
-- Solution: Track affected users, send professional emails,
-- collect real payments, without revealing the test mode mistake.
-- ============================================

SELECT '
╔═══════════════════════════════════════════════════════════╗
║     PAYMENT CORRECTION MODULE - SETUP STARTING            ║
║                                                           ║
║  Date Range: October 11 - December 13, 2025              ║
║  Estimated Affected Users: ~560                           ║
║  Deadline: December 19, 2025                              ║
╚═══════════════════════════════════════════════════════════╝
' AS setup_header;

-- ============================================
-- STEP 1: CREATE payment_corrections TABLE
-- ============================================

SELECT '⏳ Step 1: Creating payment_corrections tracking table...' AS status;

CREATE TABLE IF NOT EXISTS `payment_corrections` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT 'FK to portal_users',
  `correction_code` varchar(30) NOT NULL COMMENT 'Unique code (PC-20251213-0001)',
  `original_payment_id` bigint(20) UNSIGNED NULL COMMENT 'FK to original test payment',
  `corrected_payment_id` bigint(20) UNSIGNED NULL COMMENT 'FK to real payment after correction',
  `original_amount` decimal(10,2) NOT NULL,
  `original_razorpay_id` varchar(100) NULL COMMENT 'Original test payment Razorpay ID',
  `status` enum('pending','email_sent','reminded','completed','expired','waived') DEFAULT 'pending',
  `deadline_date` datetime NOT NULL COMMENT 'Dec 19, 2025 deadline',
  `grace_deadline_date` datetime NULL COMMENT 'Extended deadline (Dec 26)',
  
  -- Email tracking
  `first_email_sent_at` timestamp NULL DEFAULT NULL,
  `first_email_status` enum('pending','sent','delivered','opened','failed','bounced') DEFAULT 'pending',
  `first_email_error` text NULL,
  `first_email_mailgun_id` varchar(100) NULL COMMENT 'Mailgun message ID for tracking',
  
  `reminder_email_sent_at` timestamp NULL DEFAULT NULL,
  `reminder_email_status` enum('pending','sent','delivered','opened','failed','bounced') DEFAULT 'pending',
  `reminder_email_error` text NULL,
  `reminder_email_mailgun_id` varchar(100) NULL,
  
  -- User activity tracking
  `payment_completed_at` timestamp NULL DEFAULT NULL,
  `last_login_after_email` timestamp NULL DEFAULT NULL,
  `user_viewed_notice` tinyint(1) DEFAULT 0,
  `user_clicked_payment_link` tinyint(1) DEFAULT 0,
  `user_viewed_notice_count` int DEFAULT 0,
  
  -- Admin management
  `admin_notes` text DEFAULT NULL,
  `waived_by` bigint(20) UNSIGNED NULL COMMENT 'Admin who waived',
  `waived_at` timestamp NULL DEFAULT NULL,
  `waiver_reason` text DEFAULT NULL,
  `tier_level` tinyint DEFAULT 1 COMMENT 'Escalation tier (1=initial, 2=grace, 3=hold)',
  
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `correction_code` (`correction_code`),
  UNIQUE KEY `user_id_unique` (`user_id`),
  KEY `idx_user_status` (`user_id`,`status`),
  KEY `idx_status_deadline` (`status`,`deadline_date`),
  KEY `idx_email_tracking` (`first_email_status`, `reminder_email_status`),
  KEY `idx_tier` (`tier_level`, `status`),
  KEY `idx_original_payment` (`original_payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Tracks payment correction requests and email delivery';

SELECT '✓ Step 1 Complete: payment_corrections table created' AS status;

-- ============================================
-- STEP 2: ADD COLUMNS TO payments TABLE
-- ============================================

SELECT '⏳ Step 2: Adding tracking columns to payments table...' AS status;

-- Add payment_mode column
SET @check_column = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'payments' 
    AND COLUMN_NAME = 'payment_mode'
);

SET @sql = IF(@check_column = 0,
    'ALTER TABLE `payments` ADD COLUMN `payment_mode` ENUM("test","live") DEFAULT "live" COMMENT "Test or live mode" AFTER `status`',
    'SELECT "✓ payment_mode column already exists" AS msg'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add is_correction column
SET @check_column = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'payments' 
    AND COLUMN_NAME = 'is_correction'
);

SET @sql = IF(@check_column = 0,
    'ALTER TABLE `payments` ADD COLUMN `is_correction` TINYINT(1) DEFAULT 0 COMMENT "Is this a correction payment" AFTER `payment_mode`',
    'SELECT "✓ is_correction column already exists" AS msg'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add corrects_payment_id column
SET @check_column = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'payments' 
    AND COLUMN_NAME = 'corrects_payment_id'
);

SET @sql = IF(@check_column = 0,
    'ALTER TABLE `payments` ADD COLUMN `corrects_payment_id` BIGINT(20) UNSIGNED NULL COMMENT "Original payment being corrected" AFTER `is_correction`',
    'SELECT "✓ corrects_payment_id column already exists" AS msg'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes for performance
SET @check_index = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'payments' 
    AND INDEX_NAME = 'idx_mode_correction'
);

SET @sql = IF(@check_index = 0,
    'ALTER TABLE `payments` ADD INDEX `idx_mode_correction` (`payment_mode`,`is_correction`)',
    'SELECT "✓ idx_mode_correction already exists" AS msg'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add date range index
SET @check_index = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'payments' 
    AND INDEX_NAME = 'idx_date_range'
);

SET @sql = IF(@check_index = 0,
    'ALTER TABLE `payments` ADD INDEX `idx_date_range` (`created_at`, `updated_at`)',
    'SELECT "✓ idx_date_range already exists" AS msg'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT '✓ Step 2 Complete: Columns added to payments table' AS status;

-- ============================================
-- STEP 3: CREATE payment_correction_logs TABLE
-- ============================================

SELECT '⏳ Step 3: Creating audit log table...' AS status;

CREATE TABLE IF NOT EXISTS `payment_correction_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `correction_id` bigint(20) UNSIGNED NOT NULL COMMENT 'FK to payment_corrections',
  `action` varchar(100) NOT NULL COMMENT 'email_sent, status_changed, payment_completed, etc.',
  `details` text NULL COMMENT 'JSON or text details',
  `performed_by` bigint(20) UNSIGNED NULL COMMENT 'Admin ID if applicable (NULL = system)',
  `ip_address` varchar(45) NULL,
  `user_agent` varchar(255) NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_correction` (`correction_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Audit log for payment correction activities';

SELECT '✓ Step 3 Complete: payment_correction_logs table created' AS status;

-- ============================================
-- STEP 4: IDENTIFY AND MARK TEST PAYMENTS
-- ============================================

SELECT '⏳ Step 4: Identifying test payments (Oct 11 - Dec 13, 2025)...' AS status;

-- Mark test payments based on date range
UPDATE `payments` 
SET `payment_mode` = 'test' 
WHERE `status` = 'captured'
  AND (
    (`created_at` >= '2025-10-11 00:00:00' AND `created_at` <= '2025-12-13 11:04:00')
    OR (`updated_at` >= '2025-10-11 00:00:00' AND `updated_at` <= '2025-12-13 11:04:00')
  );

-- Count affected payments and users
SELECT 
    COUNT(*) AS test_payments_marked,
    COUNT(DISTINCT user_id) AS affected_users_count
FROM payments 
WHERE payment_mode = 'test';

SELECT '✓ Step 4 Complete: Test payments identified and marked' AS status;

-- ============================================
-- STEP 5: CREATE CORRECTION RECORDS
-- ============================================

SELECT '⏳ Step 5: Creating correction records for affected users...' AS status;

-- Generate correction records for each affected user
INSERT INTO `payment_corrections` (
    `user_id`,
    `correction_code`,
    `original_payment_id`,
    `original_amount`,
    `original_razorpay_id`,
    `status`,
    `deadline_date`,
    `grace_deadline_date`,
    `tier_level`,
    `created_at`,
    `updated_at`
)
SELECT 
    user_id,
    CONCAT('PC-', DATE_FORMAT(NOW(), '%Y%m%d'), '-', LPAD(row_num, 4, '0')) as correction_code,
    original_payment_id,
    original_amount,
    original_razorpay_id,
    'pending' as status,
    '2025-12-19 23:59:59' as deadline_date,
    '2025-12-26 23:59:59' as grace_deadline_date,
    1 as tier_level,
    NOW() as created_at,
    NOW() as updated_at
FROM (
    SELECT 
        p.user_id,
        p.id as original_payment_id,
        p.amount as original_amount,
        p.payment_id as original_razorpay_id,
        ROW_NUMBER() OVER (ORDER BY p.user_id) as row_num
    FROM payments p
    WHERE p.payment_mode = 'test'
      AND p.status = 'captured'
      AND p.user_id IS NOT NULL
      AND p.user_id NOT IN (SELECT user_id FROM payment_corrections WHERE user_id IS NOT NULL)
    GROUP BY p.user_id
) AS numbered_records;

-- Show count of created records
SELECT COUNT(*) AS correction_records_created FROM payment_corrections;

SELECT '✓ Step 5 Complete: Correction records created for all affected users' AS status;

-- ============================================
-- STEP 6: VERIFICATION QUERIES
-- ============================================

SELECT '
╔═══════════════════════════════════════════════════════════╗
║                    VERIFICATION RESULTS                   ║
╚═══════════════════════════════════════════════════════════╝
' AS verification_header;

-- 1. Summary statistics
SELECT 
    'SUMMARY' as category,
    (SELECT COUNT(*) FROM payments WHERE payment_mode = 'test') as test_payments,
    (SELECT COUNT(DISTINCT user_id) FROM payments WHERE payment_mode = 'test') as affected_users,
    (SELECT COUNT(*) FROM payment_corrections) as correction_records,
    (SELECT COUNT(*) FROM payment_corrections WHERE status = 'pending') as pending_corrections;

-- 2. Sample affected users (first 10)
SELECT 
    'SAMPLE USERS' as info,
    pc.correction_code,
    pu.fname,
    pu.lname,
    pu.email,
    pc.original_amount,
    pc.deadline_date,
    pc.status
FROM payment_corrections pc
INNER JOIN portal_users pu ON pu.id = pc.user_id
LIMIT 10;

-- 3. Check for duplicates (should be 0)
SELECT 
    'DUPLICATE CHECK' as info,
    user_id,
    COUNT(*) as correction_count
FROM payment_corrections
GROUP BY user_id
HAVING COUNT(*) > 1;

-- 4. Status distribution
SELECT 
    'STATUS DISTRIBUTION' as info,
    status,
    COUNT(*) as count,
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM payment_corrections), 2), '%') as percentage
FROM payment_corrections
GROUP BY status;

-- ============================================
-- STEP 7: CREATE HELPER VIEWS (OPTIONAL)
-- ============================================

SELECT '⏳ Step 7: Creating helper views for quick access...' AS status;

-- View for admin dashboard overview
CREATE OR REPLACE VIEW `v_payment_correction_overview` AS
SELECT 
    COUNT(*) as total_corrections,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
    SUM(CASE WHEN status = 'email_sent' THEN 1 ELSE 0 END) as email_sent,
    SUM(CASE WHEN status = 'reminded' THEN 1 ELSE 0 END) as reminded,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
    SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) as expired,
    SUM(CASE WHEN status = 'waived' THEN 1 ELSE 0 END) as waived,
    SUM(original_amount) as total_amount,
    SUM(CASE WHEN status = 'completed' THEN original_amount ELSE 0 END) as recovered_amount,
    SUM(CASE WHEN first_email_status = 'delivered' THEN 1 ELSE 0 END) as emails_delivered,
    SUM(CASE WHEN first_email_status = 'opened' THEN 1 ELSE 0 END) as emails_opened,
    SUM(CASE WHEN first_email_status = 'failed' THEN 1 ELSE 0 END) as emails_failed
FROM payment_corrections;

-- View for detailed user list
CREATE OR REPLACE VIEW `v_payment_correction_users` AS
SELECT 
    pc.id,
    pc.correction_code,
    pc.user_id,
    pu.fname,
    pu.lname,
    pu.email,
    pu.mobile,
    pc.original_amount,
    pc.status,
    pc.deadline_date,
    pc.first_email_sent_at,
    pc.first_email_status,
    pc.reminder_email_sent_at,
    pc.reminder_email_status,
    pc.payment_completed_at,
    pc.tier_level,
    p.created_at as original_payment_date,
    p.payment_id as original_razorpay_id
FROM payment_corrections pc
INNER JOIN portal_users pu ON pu.id = pc.user_id
LEFT JOIN payments p ON p.id = pc.original_payment_id;

SELECT '✓ Step 7 Complete: Helper views created' AS status;

-- ============================================
-- FINAL SUMMARY
-- ============================================

SELECT '
╔═══════════════════════════════════════════════════════════╗
║         PAYMENT CORRECTION SETUP COMPLETED!               ║
║                                                           ║
║  ✓ Tables created: payment_corrections, logs             ║
║  ✓ Columns added: 3 columns to payments table            ║
║  ✓ Test payments marked: All identified                  ║
║  ✓ Correction records: Created for all users             ║
║  ✓ Views created: Dashboard and user list views          ║
║                                                           ║
║  Deadline: December 19, 2025 (6 days remaining)          ║
║  Grace Period: Until December 26, 2025                   ║
║                                                           ║
║  NEXT STEPS:                                              ║
║  1. Run Laravel migrations (php artisan migrate)         ║
║  2. Configure Mailgun credentials in .env                ║
║  3. Set up queue workers (php artisan queue:work)        ║
║  4. Test email sending with test addresses               ║
║  5. Review admin dashboard                               ║
║  6. Send first batch of correction emails                ║
╚═══════════════════════════════════════════════════════════╝
' AS completion_message;

-- Show final statistics
SELECT * FROM v_payment_correction_overview;

-- ============================================
-- QUICK REFERENCE QUERIES
-- ============================================
-- Copy these for future use:

-- Get all pending corrections:
-- SELECT * FROM v_payment_correction_users WHERE status = 'pending';

-- Get users who haven't opened email:
-- SELECT * FROM v_payment_correction_users 
-- WHERE first_email_status != 'opened' AND first_email_sent_at IS NOT NULL;

-- Get corrections needing reminders:
-- SELECT * FROM v_payment_correction_users 
-- WHERE status = 'email_sent' 
-- AND reminder_email_sent_at IS NULL 
-- AND first_email_sent_at < DATE_SUB(NOW(), INTERVAL 3 DAY);

-- Get expired corrections (past deadline):
-- SELECT * FROM v_payment_correction_users 
-- WHERE status NOT IN ('completed', 'waived') 
-- AND deadline_date < NOW();
