-- Add missing loan_app_id column to utils_notification table
ALTER TABLE utils_notification ADD COLUMN loan_app_id CHAR(32) NULL;

-- Add foreign key constraint
ALTER TABLE utils_notification ADD CONSTRAINT utils_notification_loan_app_id_fk 
    FOREIGN KEY (loan_app_id) REFERENCES loan_applications(id) ON DELETE CASCADE;

-- Add related_loan_id column if it doesn't exist
ALTER TABLE utils_notification ADD COLUMN related_loan_id CHAR(32) NULL;

-- Add foreign key constraint for related_loan
ALTER TABLE utils_notification ADD CONSTRAINT utils_notification_related_loan_id_fk 
    FOREIGN KEY (related_loan_id) REFERENCES loans(id) ON DELETE CASCADE;

-- Add action_required column if it doesn't exist
ALTER TABLE utils_notification ADD COLUMN action_required TINYINT(1) DEFAULT 0;

-- Add alert_data column if it doesn't exist
ALTER TABLE utils_notification ADD COLUMN alert_data JSON NULL;