

-- ============================================
-- 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';

-- ============================================
-- 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
-- ============================================
