-- ============================================
-- Payment Recovery System - Tables WITHOUT Foreign Keys
-- Created: December 19, 2025
-- For: Hostgator Production Import (Step 1)
-- Use this if you get "Failed to open the referenced table" error
-- ============================================

-- Drop existing tables if they exist

-- Create tables without foreign key constraints
CREATE TABLE `uid_registration_recoveries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `payment_id` bigint unsigned NOT NULL,
  `recovery_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','email_sent','payment_completed','expired') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `email_sent` tinyint(1) NOT NULL DEFAULT '0',
  `email_sent_at` timestamp NULL DEFAULT NULL,
  `email_status` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_open_count` int NOT NULL DEFAULT '0',
  `email_last_opened_at` timestamp NULL DEFAULT NULL,
  `payment_completed_at` timestamp NULL DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid_registration_recoveries_recovery_code_unique` (`recovery_code`),
  KEY `uid_registration_recoveries_user_id_foreign` (`user_id`),
  KEY `uid_registration_recoveries_payment_id_foreign` (`payment_id`),
  KEY `uid_registration_recoveries_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `membership_renewal_recoveries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `payment_id` bigint unsigned NOT NULL,
  `recovery_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `old_expiry_date` date DEFAULT NULL,
  `status` enum('pending','email_sent','payment_completed','expired') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `email_sent` tinyint(1) NOT NULL DEFAULT '0',
  `email_sent_at` timestamp NULL DEFAULT NULL,
  `email_status` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_open_count` int NOT NULL DEFAULT '0',
  `email_last_opened_at` timestamp NULL DEFAULT NULL,
  `payment_completed_at` timestamp NULL DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `membership_renewal_recoveries_recovery_code_unique` (`recovery_code`),
  KEY `membership_renewal_recoveries_user_id_foreign` (`user_id`),
  KEY `membership_renewal_recoveries_payment_id_foreign` (`payment_id`),
  KEY `membership_renewal_recoveries_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `certificate_payment_recoveries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `payment_id` bigint unsigned NOT NULL,
  `belt_test_id` bigint unsigned DEFAULT NULL,
  `recovery_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','email_sent','payment_completed','expired') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `email_sent` tinyint(1) NOT NULL DEFAULT '0',
  `email_sent_at` timestamp NULL DEFAULT NULL,
  `email_status` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_open_count` int NOT NULL DEFAULT '0',
  `email_last_opened_at` timestamp NULL DEFAULT NULL,
  `payment_completed_at` timestamp NULL DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `certificate_payment_recoveries_recovery_code_unique` (`recovery_code`),
  KEY `certificate_payment_recoveries_user_id_foreign` (`user_id`),
  KEY `certificate_payment_recoveries_payment_id_foreign` (`payment_id`),
  KEY `certificate_payment_recoveries_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Step 2: Add Foreign Key Constraints (OPTIONAL)
-- Run these AFTER confirming payments and portal_users tables exist
-- Only run if you want referential integrity enforcement
-- ============================================

-- Add foreign keys for uid_registration_recoveries
-- ALTER TABLE `uid_registration_recoveries`
--   ADD CONSTRAINT `uid_registration_recoveries_payment_id_foreign` 
--     FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
--   ADD CONSTRAINT `uid_registration_recoveries_user_id_foreign` 
--     FOREIGN KEY (`user_id`) REFERENCES `portal_users` (`id`) ON DELETE CASCADE;

-- Add foreign keys for membership_renewal_recoveries
-- ALTER TABLE `membership_renewal_recoveries`
--   ADD CONSTRAINT `membership_renewal_recoveries_payment_id_foreign` 
--     FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
--   ADD CONSTRAINT `membership_renewal_recoveries_user_id_foreign` 
--     FOREIGN KEY (`user_id`) REFERENCES `portal_users` (`id`) ON DELETE CASCADE;

-- Add foreign keys for certificate_payment_recoveries
-- ALTER TABLE `certificate_payment_recoveries`
--   ADD CONSTRAINT `certificate_payment_recoveries_payment_id_foreign` 
--     FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
--   ADD CONSTRAINT `certificate_payment_recoveries_user_id_foreign` 
--     FOREIGN KEY (`user_id`) REFERENCES `portal_users` (`id`) ON DELETE CASCADE;

-- ============================================
-- Verification Queries
-- ============================================
SELECT 'Tables Created Successfully' as status;

SHOW TABLES LIKE '%_recoveries';

-- ============================================
-- POPULATE RECOVERY TABLES FROM PAYMENTS
-- ============================================
-- Insert recovery records for test mode payments within date range
-- Date Range: 2025-10-11 00:00:00 to 2025-12-13 11:04:00

-- -----------------------------------------------
-- Populate: UID Registration Recoveries
-- -----------------------------------------------
INSERT INTO uid_registration_recoveries 
    (user_id, payment_id, recovery_code, status, email_sent, created_at, updated_at)
SELECT 
    p.user_id,
    p.id as payment_id,
    CONCAT('UID-', DATE_FORMAT(p.created_at, '%Y%m%d'), '-', p.id) as recovery_code,
    'pending' as status,
    0 as email_sent,
    NOW() as created_at,
    NOW() as updated_at
FROM payments p
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 uid_registration_recoveries ur 
    WHERE ur.payment_id = p.id
);

-- -----------------------------------------------
-- Populate: Membership Renewal Recoveries
-- -----------------------------------------------
INSERT INTO membership_renewal_recoveries 
    (user_id, payment_id, recovery_code, old_expiry_date, status, email_sent, created_at, updated_at)
SELECT 
    p.user_id,
    p.id as payment_id,
    CONCAT('MEM-', DATE_FORMAT(p.created_at, '%Y%m%d'), '-', p.id) as recovery_code,
    u.expiry_date as old_expiry_date,
    'pending' as status,
    0 as email_sent,
    NOW() as created_at,
    NOW() as updated_at
FROM payments p
JOIN portal_users u ON p.user_id = u.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'
AND NOT EXISTS (
    SELECT 1 FROM membership_renewal_recoveries mr 
    WHERE mr.payment_id = p.id
);

-- -----------------------------------------------
-- Populate: Certificate Payment Recoveries
-- -----------------------------------------------
INSERT INTO certificate_payment_recoveries 
    (user_id, payment_id, belt_test_id, recovery_code, status, email_sent, created_at, updated_at)
SELECT 
    p.user_id,
    p.id as payment_id,
    NULL as belt_test_id,
    CONCAT('CERT-', DATE_FORMAT(p.created_at, '%Y%m%d'), '-', p.id) as recovery_code,
    'pending' as status,
    0 as email_sent,
    NOW() as created_at,
    NOW() as updated_at
FROM payments p
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'
AND NOT EXISTS (
    SELECT 1 FROM certificate_payment_recoveries cr 
    WHERE cr.payment_id = p.id
);

-- -----------------------------------------------
-- Verify Population Results
-- -----------------------------------------------
SELECT 
    'UID Registration' as recovery_type,
    COUNT(*) as total_records,
    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 = 'payment_completed' THEN 1 ELSE 0 END) as completed
FROM uid_registration_recoveries
UNION ALL
SELECT 
    'Membership Renewal' as recovery_type,
    COUNT(*) as total_records,
    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 = 'payment_completed' THEN 1 ELSE 0 END) as completed
FROM membership_renewal_recoveries
UNION ALL
SELECT 
    'Certificate Payment' as recovery_type,
    COUNT(*) as total_records,
    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 = 'payment_completed' THEN 1 ELSE 0 END) as completed
FROM certificate_payment_recoveries;

-- ============================================
-- Record Count Summary
-- ============================================
SELECT 'uid_registration_recoveries' as table_name, COUNT(*) as record_count 
FROM uid_registration_recoveries
UNION ALL
SELECT 'membership_renewal_recoveries' as table_name, COUNT(*) as record_count 
FROM membership_renewal_recoveries
UNION ALL
SELECT 'certificate_payment_recoveries' as table_name, COUNT(*) as record_count 
FROM certificate_payment_recoveries;

-- ============================================
-- IMPORT COMPLETE
-- ============================================
-- Recovery tables created and populated successfully!
-- 
-- NEXT STEP:
-- Import payment_recovery_portal_users_fix.sql to update portal_users table
--
-- Expected Results:
-- - 590 UID Registration records
-- - 76 Membership Renewal records
-- - ~0 Certificate Payment records
-- ============================================
