-- Migration to add 'approve' permission to existing joint account members
-- This ensures all partners can approve transactions they didn't initiate

-- 1. Add 'approve' to any array that's missing it
UPDATE joint_account_members 
SET permissions = JSON_ARRAY_APPEND(permissions, '$', 'approve') 
WHERE permissions IS NOT NULL 
AND JSON_TYPE(permissions) = 'ARRAY' 
AND NOT JSON_CONTAINS(permissions, '"approve"');

-- 2. Specifically ensure Primary members have the 'approve' and 'manage' permissions
-- This handles legacy primary members who might have had a limited set
UPDATE joint_account_members 
SET permissions = '["view", "trade", "withdraw", "manage", "approve"]' 
WHERE role = 'primary';

-- 3. Default permissions for any NULL records (safety check)
UPDATE joint_account_members 
SET permissions = '["view", "trade", "withdraw", "approve"]' 
WHERE role = 'secondary' AND (permissions IS NULL OR permissions = 'null');
