-- Migration: Fix frequency caps and notification status tracking
-- Purpose: 
-- 1. Support both workers and hirers in frequency caps by removing user_id FK and adding user_type
-- 2. Add provider_notification_id to notifications table for reliable status tracking with OneSignal

USE aayusha_backend;

-- 1. Fix frequency_caps table
-- Remove foreign key constraint if it exists
SET @constraint_name_fc = (
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'frequency_caps' 
    AND COLUMN_NAME = 'user_id' 
    AND REFERENCED_TABLE_NAME = 'users'
    LIMIT 1
);

SET @query_fc = IF(@constraint_name_fc IS NOT NULL, 
    CONCAT('ALTER TABLE frequency_caps DROP FOREIGN KEY ', @constraint_name_fc), 
    'SELECT "No FK on frequency_caps.user_id found"');

PREPARE stmt_fc FROM @query_fc;
EXECUTE stmt_fc;
DEALLOCATE PREPARE stmt_fc;

-- Add user_type column to frequency_caps if it doesn't exist
SET @fc_user_type_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'frequency_caps' 
    AND COLUMN_NAME = 'user_type'
);

SET @query_add_fc_type = IF(@fc_user_type_exists = 0, 
    'ALTER TABLE frequency_caps ADD COLUMN user_type VARCHAR(20) DEFAULT "Hirer" AFTER user_id', 
    'SELECT "user_type already exists in frequency_caps"');

PREPARE stmt_add_fc_type FROM @query_add_fc_type;
EXECUTE stmt_add_fc_type;
DEALLOCATE PREPARE stmt_add_fc_type;

-- Update unique index for frequency_caps to include user_type
ALTER TABLE frequency_caps DROP INDEX idx_user_date;
ALTER TABLE frequency_caps ADD UNIQUE KEY idx_user_type_date (user_id, user_type, cap_date);

-- 2. Fix notifications table for status tracking
-- Add provider_notification_id column to notifications if it doesn't exist
SET @notif_provider_id_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'notifications' 
    AND COLUMN_NAME = 'provider_notification_id'
);

SET @query_add_notif_provider_id = IF(@notif_provider_id_exists = 0, 
    'ALTER TABLE notifications ADD COLUMN provider_notification_id VARCHAR(255) NULL AFTER external_id', 
    'SELECT "provider_notification_id already exists in notifications"');

PREPARE stmt_add_notif_provider_id FROM @query_add_notif_provider_id;
EXECUTE stmt_add_notif_provider_id;
DEALLOCATE PREPARE stmt_add_notif_provider_id;

-- Add index for provider_notification_id
CREATE INDEX idx_provider_notification_id ON notifications(provider_notification_id);

-- Also add provider_notification_id to notification_logs if it exists
SET @notif_logs_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'aayusha_backend' 
    AND TABLE_NAME = 'notification_logs'
);

SET @query_add_notif_logs_provider_id = IF(@notif_logs_exists > 0, 
    'ALTER TABLE notification_logs ADD COLUMN provider_notification_id VARCHAR(255) NULL AFTER external_id', 
    'SELECT "notification_logs table does not exist or already updated"');

PREPARE stmt_add_notif_logs_provider_id FROM @query_add_notif_logs_provider_id;
EXECUTE stmt_add_notif_logs_provider_id;
DEALLOCATE PREPARE stmt_add_notif_logs_provider_id;
