-- Migrate form_elements to jsonb column on forms
ALTER TABLE forms ADD COLUMN IF NOT EXISTS elements jsonb DEFAULT NULL;

UPDATE forms
SET elements = (
    SELECT jsonb_agg(
        jsonb_build_object(
            'id', fe.id,
            'tenant_id', fe.tenant_id,
            'branch_id', fe.branch_id,
            'label', fe.label,
            'field_name', fe.field_name,
            'data_column', fe.data_column,
            'type_id', fe.type_id,
            'options_table', fe.options_table,
            'options_label_column', fe.options_label_column,
            'options_value_column', fe.options_value_column,
            'is_required', fe.is_required,
            'validation', fe.validation,
            'order_index', fe.order_index,
            'options', (
                SELECT jsonb_agg(jsonb_build_object(
                    'id', eo.id,
                    'tenant_id', eo.tenant_id,
                    'branch_id', eo.branch_id,
                    'display_text', eo.display_text,
                    'value', eo.value
                )) FROM element_options eo
                WHERE eo.element_id = fe.id AND eo.isDeleted = false
            )
        )
    )
    FROM form_elements fe
    WHERE fe.form_id = forms.id AND fe.isDeleted = false
)
WHERE EXISTS (SELECT 1 FROM form_elements fe WHERE fe.form_id = forms.id AND fe.isDeleted = false);

