-- Update existing wills for clients who have is_joint_will = 1
-- This script copies joint will information from clients to their wills

-- Update will #133 specifically for client ID 222
UPDATE wills w
JOIN clients c ON w.client_id = c.id
SET 
    w.is_joint = 1,
    w.joint_client_id = c.joint_client_id,
    w.joint_type = c.joint_type,
    w.joint_testator_details = JSON_OBJECT(
        'primary', JSON_OBJECT(
            'full_name', c.joint_person_name,
            'ic', c.joint_person_ic_number,
            'address', c.joint_person_address,
            'email', c.joint_person_email,
            'phone', c.joint_person_phone,
            'marital_status', c.joint_person_marital_status
        )
    )
WHERE w.id = 133 AND c.id = 222 AND c.is_joint_will = 1;

-- Update ALL existing wills for clients who have is_joint_will = 1
UPDATE wills w
JOIN clients c ON w.client_id = c.id
SET 
    w.is_joint = 1,
    w.joint_client_id = c.joint_client_id,
    w.joint_type = c.joint_type,
    w.joint_testator_details = JSON_OBJECT(
        'primary', JSON_OBJECT(
            'full_name', c.joint_person_name,
            'ic', c.joint_person_ic_number,
            'address', c.joint_person_address,
            'email', c.joint_person_email,
            'phone', c.joint_person_phone,
            'marital_status', c.joint_person_marital_status
        )
    )
WHERE c.is_joint_will = 1 
  AND c.joint_person_name IS NOT NULL 
  AND (w.is_joint IS NULL OR w.is_joint = 0);