-- ============================================================================
-- DATABASE FIX: SMS & WhatsApp Notification System
-- ============================================================================
-- Date: December 24, 2025
-- Description: 
--   1. Fixes view syntax error (nullunion → null UNION ALL SELECT)
--   2. Creates notification_logs table for SMS/WhatsApp/Email tracking
--   3. Adds SMS and WhatsApp tracking columns to recovery tables
-- 
-- IMPORTANT: 
--   - Backup your database before running this script!
--   - This script works on ANY database (production, local, staging, testing)
--   - Select your target database before importing
--   - Safe to re-run - checks if columns exist before adding
-- ============================================================================

-- Use currently selected database (works anywhere)
-- If importing via phpMyAdmin: Select database first, then import this file
-- If importing via command line: Use -D flag: mysql -D your_database_name < this_file.sql

-- ============================================================================
-- PART 1: Fix View Syntax Error
-- ============================================================================

DROP VIEW IF EXISTS `v_high_priority_pending`;

-- In case an object with the same name exists as a TABLE, drop it to avoid #1347

CREATE ALGORITHM=UNDEFINED DEFINER=CURRENT_USER 
SQL SECURITY INVOKER 
VIEW `v_high_priority_pending` AS 
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 

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 

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;

-- ============================================================================
-- PART 2: Create notification_logs Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS `notification_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `notification_type` varchar(100) NOT NULL COMMENT 'uid_registration, membership_renewal_30d, membership_renewal_7d, etc.',
  `channel` varchar(20) NOT NULL COMMENT 'sms, whatsapp, email',
  `provider` varchar(50) DEFAULT NULL COMMENT 'gupshup, twilio, msg91, wati, interakt',
  `recipient_mobile` varchar(20) DEFAULT NULL,
  `recipient_email` varchar(191) DEFAULT NULL,
  `message_content` text DEFAULT NULL,
  `template_id` varchar(100) DEFAULT NULL,
  `template_name` varchar(191) DEFAULT NULL,
  
  -- Delivery tracking
  `status` enum('queued','sent','delivered','failed','bounced') NOT NULL DEFAULT 'queued',
  `message_id` varchar(191) DEFAULT NULL COMMENT 'Provider message ID',
  `error_message` text DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `failed_at` timestamp NULL DEFAULT NULL,
  
  -- Engagement tracking
  `opened` tinyint(1) NOT NULL DEFAULT 0,
  `opened_at` timestamp NULL DEFAULT NULL,
  `open_count` int(11) NOT NULL DEFAULT 0,
  `clicked` tinyint(1) NOT NULL DEFAULT 0,
  `clicked_at` timestamp NULL DEFAULT NULL,
  `click_count` int(11) NOT NULL DEFAULT 0,
  
  -- Context
  `metadata` json DEFAULT NULL COMMENT 'Store recovery_id, payment_id, etc.',
  `retry_count` int(11) NOT NULL DEFAULT 0,
  `cost` decimal(10,4) DEFAULT NULL COMMENT 'Cost per message',
  
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  KEY `notification_logs_user_id_index` (`user_id`),
  KEY `notification_logs_notification_type_index` (`notification_type`),
  KEY `notification_logs_channel_status_index` (`channel`,`status`),
  KEY `notification_logs_sent_at_index` (`sent_at`),
  KEY `notification_logs_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Comprehensive notification tracking for SMS/WhatsApp/Email';

-- Note: Foreign key to portal_users table not added to ensure compatibility
-- If you want to add it manually, run this after import:
-- ALTER TABLE `notification_logs` ADD CONSTRAINT `notification_logs_user_id_foreign` 
-- FOREIGN KEY (`user_id`) REFERENCES `portal_users` (`id`) ON DELETE CASCADE;

-- ============================================================================
-- PART 3: Add SMS & WhatsApp Tracking Columns to uid_registration_recoveries
-- ============================================================================

-- Check if columns exist before adding (safe for re-running)
SET @dbname = DATABASE();
SET @tablename = 'uid_registration_recoveries';

-- Add SMS tracking columns
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename 
                   AND COLUMN_NAME = 'sms_sent');
SET @query = IF(@col_exists = 0,
    'ALTER TABLE `uid_registration_recoveries` 
     ADD COLUMN `sms_sent` tinyint(1) NOT NULL DEFAULT 0 AFTER `email_last_opened_at`,
     ADD COLUMN `sms_sent_at` timestamp NULL DEFAULT NULL AFTER `sms_sent`,
     ADD COLUMN `sms_status` varchar(50) DEFAULT NULL AFTER `sms_sent_at`,
     ADD COLUMN `sms_message_id` varchar(191) DEFAULT NULL AFTER `sms_status`,
     ADD COLUMN `sms_provider` varchar(50) DEFAULT NULL AFTER `sms_message_id`,
     ADD COLUMN `whatsapp_sent` tinyint(1) NOT NULL DEFAULT 0 AFTER `sms_provider`,
     ADD COLUMN `whatsapp_sent_at` timestamp NULL DEFAULT NULL AFTER `whatsapp_sent`,
     ADD COLUMN `whatsapp_status` varchar(50) DEFAULT NULL AFTER `whatsapp_sent_at`,
     ADD COLUMN `whatsapp_message_id` varchar(191) DEFAULT NULL AFTER `whatsapp_status`,
     ADD COLUMN `whatsapp_provider` varchar(50) DEFAULT NULL AFTER `whatsapp_message_id`,
     ADD COLUMN `follow_up_count` int(11) NOT NULL DEFAULT 0 AFTER `whatsapp_provider`,
     ADD COLUMN `last_follow_up_at` timestamp NULL DEFAULT NULL AFTER `follow_up_count`',
    'SELECT "SMS/WhatsApp columns already exist in uid_registration_recoveries" AS Info');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- PART 4: Add SMS & WhatsApp Tracking Columns to membership_renewal_recoveries
-- ============================================================================

SET @tablename = 'membership_renewal_recoveries';

-- Add SMS tracking columns
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename 
                   AND COLUMN_NAME = 'sms_sent');
SET @query = IF(@col_exists = 0,
    'ALTER TABLE `membership_renewal_recoveries` 
     ADD COLUMN `sms_sent` tinyint(1) NOT NULL DEFAULT 0 AFTER `email_last_opened_at`,
     ADD COLUMN `sms_sent_at` timestamp NULL DEFAULT NULL AFTER `sms_sent`,
     ADD COLUMN `sms_status` varchar(50) DEFAULT NULL AFTER `sms_sent_at`,
     ADD COLUMN `sms_message_id` varchar(191) DEFAULT NULL AFTER `sms_status`,
     ADD COLUMN `sms_provider` varchar(50) DEFAULT NULL AFTER `sms_message_id`,
     ADD COLUMN `whatsapp_sent` tinyint(1) NOT NULL DEFAULT 0 AFTER `sms_provider`,
     ADD COLUMN `whatsapp_sent_at` timestamp NULL DEFAULT NULL AFTER `whatsapp_sent`,
     ADD COLUMN `whatsapp_status` varchar(50) DEFAULT NULL AFTER `whatsapp_sent_at`,
     ADD COLUMN `whatsapp_message_id` varchar(191) DEFAULT NULL AFTER `whatsapp_status`,
     ADD COLUMN `whatsapp_provider` varchar(50) DEFAULT NULL AFTER `whatsapp_message_id`,
     ADD COLUMN `follow_up_count` int(11) NOT NULL DEFAULT 0 AFTER `whatsapp_provider`,
     ADD COLUMN `last_follow_up_at` timestamp NULL DEFAULT NULL AFTER `follow_up_count`',
    'SELECT "SMS/WhatsApp columns already exist in membership_renewal_recoveries" AS Info');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- PART 5: Add SMS & WhatsApp Tracking Columns to certificate_payment_recoveries
-- ============================================================================

SET @tablename = 'certificate_payment_recoveries';

-- Add SMS tracking columns
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename 
                   AND COLUMN_NAME = 'sms_sent');
SET @query = IF(@col_exists = 0,
    'ALTER TABLE `certificate_payment_recoveries` 
     ADD COLUMN `sms_sent` tinyint(1) NOT NULL DEFAULT 0 AFTER `email_last_opened_at`,
     ADD COLUMN `sms_sent_at` timestamp NULL DEFAULT NULL AFTER `sms_sent`,
     ADD COLUMN `sms_status` varchar(50) DEFAULT NULL AFTER `sms_sent_at`,
     ADD COLUMN `sms_message_id` varchar(191) DEFAULT NULL AFTER `sms_status`,
     ADD COLUMN `sms_provider` varchar(50) DEFAULT NULL AFTER `sms_message_id`,
     ADD COLUMN `whatsapp_sent` tinyint(1) NOT NULL DEFAULT 0 AFTER `sms_provider`,
     ADD COLUMN `whatsapp_sent_at` timestamp NULL DEFAULT NULL AFTER `whatsapp_sent`,
     ADD COLUMN `whatsapp_status` varchar(50) DEFAULT NULL AFTER `whatsapp_sent_at`,
     ADD COLUMN `whatsapp_message_id` varchar(191) DEFAULT NULL AFTER `whatsapp_status`,
     ADD COLUMN `whatsapp_provider` varchar(50) DEFAULT NULL AFTER `whatsapp_message_id`,
     ADD COLUMN `follow_up_count` int(11) NOT NULL DEFAULT 0 AFTER `whatsapp_provider`,
     ADD COLUMN `last_follow_up_at` timestamp NULL DEFAULT NULL AFTER `follow_up_count`',
    'SELECT "SMS/WhatsApp columns already exist in certificate_payment_recoveries" AS Info');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Check notification_logs table
SELECT 
    'notification_logs' AS table_name,
    COUNT(*) AS column_count 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'notification_logs';

-- Check uid_registration_recoveries columns
SELECT 
    'uid_registration_recoveries' AS table_name,
    COUNT(*) AS sms_whatsapp_columns 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'uid_registration_recoveries'
  AND COLUMN_NAME IN ('sms_sent', 'sms_sent_at', 'sms_status', 'whatsapp_sent', 'whatsapp_sent_at', 'whatsapp_status');

-- Check membership_renewal_recoveries columns
SELECT 
    'membership_renewal_recoveries' AS table_name,
    COUNT(*) AS sms_whatsapp_columns 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'membership_renewal_recoveries'
  AND COLUMN_NAME IN ('sms_sent', 'sms_sent_at', 'sms_status', 'whatsapp_sent', 'whatsapp_sent_at', 'whatsapp_status');

-- Check certificate_payment_recoveries columns
SELECT 
    'certificate_payment_recoveries' AS table_name,
    COUNT(*) AS sms_whatsapp_columns 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'certificate_payment_recoveries'
  AND COLUMN_NAME IN ('sms_sent', 'sms_sent_at', 'sms_status', 'whatsapp_sent', 'whatsapp_sent_at', 'whatsapp_status');

-- Check view
SELECT 
    TABLE_NAME, 
    VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'v_high_priority_pending';

-- ============================================================================
-- ROLLBACK SCRIPT (Save this separately if you need to undo changes)
-- ============================================================================
/*
-- Drop notification_logs table

-- Remove SMS/WhatsApp columns from uid_registration_recoveries
ALTER TABLE `uid_registration_recoveries` 
DROP COLUMN `sms_sent`,
DROP COLUMN `sms_sent_at`,
DROP COLUMN `sms_status`,
DROP COLUMN `sms_message_id`,
DROP COLUMN `sms_provider`,
DROP COLUMN `whatsapp_sent`,
DROP COLUMN `whatsapp_sent_at`,
DROP COLUMN `whatsapp_status`,
DROP COLUMN `whatsapp_message_id`,
DROP COLUMN `whatsapp_provider`,
DROP COLUMN `follow_up_count`,
DROP COLUMN `last_follow_up_at`;

-- Remove SMS/WhatsApp columns from membership_renewal_recoveries
ALTER TABLE `membership_renewal_recoveries` 
DROP COLUMN `sms_sent`,
DROP COLUMN `sms_sent_at`,
DROP COLUMN `sms_status`,
DROP COLUMN `sms_message_id`,
DROP COLUMN `sms_provider`,
DROP COLUMN `whatsapp_sent`,
DROP COLUMN `whatsapp_sent_at`,
DROP COLUMN `whatsapp_status`,
DROP COLUMN `whatsapp_message_id`,
DROP COLUMN `whatsapp_provider`,
DROP COLUMN `follow_up_count`,
DROP COLUMN `last_follow_up_at`;

-- Remove SMS/WhatsApp columns from certificate_payment_recoveries
ALTER TABLE `certificate_payment_recoveries` 
DROP COLUMN `sms_sent`,
DROP COLUMN `sms_sent_at`,
DROP COLUMN `sms_status`,
DROP COLUMN `sms_message_id`,
DROP COLUMN `sms_provider`,
DROP COLUMN `whatsapp_sent`,
DROP COLUMN `whatsapp_sent_at`,
DROP COLUMN `whatsapp_status`,
DROP COLUMN `whatsapp_message_id`,
DROP COLUMN `whatsapp_provider`,
DROP COLUMN `follow_up_count`,
DROP COLUMN `last_follow_up_at`;
*/

-- ============================================================================
-- COMPLETION MESSAGE
-- ============================================================================
SELECT '✓ Database update completed successfully!' AS Status,
       'View syntax fixed, notification_logs table created, SMS/WhatsApp columns added' AS Details;
