-- Run this SQL to add joint will support to the wills table
-- Execute in MySQL or via phpMyAdmin

ALTER TABLE wills 
ADD COLUMN is_joint BOOLEAN DEFAULT FALSE AFTER will_type,
ADD COLUMN joint_client_id BIGINT UNSIGNED NULL AFTER is_joint,
ADD COLUMN joint_type ENUM('spouse', 'partner', 'family') NULL AFTER joint_client_id,
ADD COLUMN joint_testator_details JSON NULL AFTER testator_details,
ADD COLUMN joint_instructions JSON NULL AFTER instructions,
ADD COLUMN joint_beneficiaries JSON NULL AFTER beneficiaries;

-- Add foreign key constraint for joint_client_id
ALTER TABLE wills 
ADD CONSTRAINT wills_joint_client_id_foreign 
FOREIGN KEY (joint_client_id) REFERENCES clients(id) ON DELETE CASCADE;

-- Add indexes for better performance
CREATE INDEX idx_wills_joint ON wills(is_joint, joint_client_id);