-- =============================================================================
-- Fix v_high_priority_pending VIEW - Syntax Error Correction
-- =============================================================================
-- Issue: Missing spaces between keywords (nullunion, allselect)
-- Solution: Drop and recreate view with proper spacing
-- =============================================================================

-- Drop the existing view if it exists
DROP VIEW IF EXISTS `v_high_priority_pending`;

-- Recreate the view with correct syntax (using CURRENT_USER as definer)
CREATE OR REPLACE
SQL SECURITY DEFINER 
VIEW `v_high_priority_pending` AS 

-- UID Registration recoveries
SELECT 
    'UID Registration' AS `type`,
    `uid_registration_recoveries`.`id` AS `id`,
    `uid_registration_recoveries`.`user_id` AS `user_id`,
    `uid_registration_recoveries`.`email_sent_at` AS `email_sent_at`,
    TO_DAYS(CURRENT_TIMESTAMP()) - TO_DAYS(`uid_registration_recoveries`.`email_sent_at`) AS `days_pending`,
    `uid_registration_recoveries`.`email_open_count` AS `email_open_count`,
    `uid_registration_recoveries`.`email_last_opened_at` AS `email_last_opened_at`
FROM `uid_registration_recoveries`
WHERE `uid_registration_recoveries`.`new_payment_id` IS NULL 
  AND `uid_registration_recoveries`.`email_sent_at` IS NOT NULL

UNION ALL

-- Membership Renewal recoveries
SELECT 
    'Membership Renewal' AS `type`,
    `membership_renewal_recoveries`.`id` AS `id`,
    `membership_renewal_recoveries`.`user_id` AS `user_id`,
    `membership_renewal_recoveries`.`email_sent_at` AS `email_sent_at`,
    TO_DAYS(CURRENT_TIMESTAMP()) - TO_DAYS(`membership_renewal_recoveries`.`email_sent_at`) AS `days_pending`,
    `membership_renewal_recoveries`.`email_open_count` AS `email_open_count`,
    `membership_renewal_recoveries`.`email_last_opened_at` AS `email_last_opened_at`
FROM `membership_renewal_recoveries`
WHERE `membership_renewal_recoveries`.`new_payment_id` IS NULL 
  AND `membership_renewal_recoveries`.`email_sent_at` IS NOT NULL

UNION ALL

-- Certificate Payment recoveries
SELECT 
    'Certificate Payment' AS `type`,
    `certificate_payment_recoveries`.`id` AS `id`,
    `certificate_payment_recoveries`.`user_id` AS `user_id`,
    `certificate_payment_recoveries`.`email_sent_at` AS `email_sent_at`,
    TO_DAYS(CURRENT_TIMESTAMP()) - TO_DAYS(`certificate_payment_recoveries`.`email_sent_at`) AS `days_pending`,
    `certificate_payment_recoveries`.`email_open_count` AS `email_open_count`,
    `certificate_payment_recoveries`.`email_last_opened_at` AS `email_last_opened_at`
FROM `certificate_payment_recoveries`
WHERE `certificate_payment_recoveries`.`new_payment_id` IS NULL 
  AND `certificate_payment_recoveries`.`email_sent_at` IS NOT NULL

ORDER BY `days_pending` DESC;

-- =============================================================================
-- Verification Query
-- =============================================================================
-- Run this to verify the view works correctly:
-- SELECT * FROM v_high_priority_pending LIMIT 10;
-- =============================================================================
