-- Add project notification settings for project leads
-- Allows project leads to subscribe to task status changes (review/done)

-- Add project_notification_settings column to store notification preferences
ALTER TABLE projects 
ADD COLUMN IF NOT EXISTS project_notification_settings JSON DEFAULT NULL
COMMENT 'Notification settings for project leads and administrators';

-- Create an index for JSON searches if needed
CREATE INDEX IF NOT EXISTS idx_projects_notification_settings 
ON projects((CAST(project_notification_settings->'$.leadNotifications.enabled' AS UNSIGNED)));

-- Update existing projects with default notification settings
UPDATE projects 
SET project_notification_settings = JSON_OBJECT(
    'leadNotifications', JSON_OBJECT(
        'enabled', false,
        'notifyOnReview', true,
        'notifyOnDone', true,
        'subscribers', JSON_ARRAY()
    ),
    'adminNotifications', JSON_OBJECT(
        'enabled', false,
        'notifyOnReview', true,
        'notifyOnDone', true,
        'subscribers', JSON_ARRAY()
    )
)
WHERE project_notification_settings IS NULL;

SELECT 'Project notification settings added successfully!' as status;