-- Migration: Fix Employee Account form for editing
-- Date: 2026-01-26
-- Description: Ensure Location dropdown works properly and Emails/Password fields
--              are handled correctly when editing employee records
-- Use proper child table structure for user_emails, user_phones, and branch_memberships

-- Update Employee Account form to ensure proper field configuration
UPDATE forms
SET elements = $form$[
    {
      "label": "Full Name",
      "field_name": "full_name",
      "data_column": "full_name",
      "type_id": 1,
      "order_index": 1,
      "required": true
    },
    {
      "label": "Email",
      "field_name": "user_emails[0].email",
      "data_column": "user_emails",
      "type": "email",
      "type_id": 1,
      "order_index": 2,
      "required": true,
      "child_table": "user_emails",
      "child_field": "email",
      "child_defaults": {"is_primary": true},
      "help_text": "Primary email address for this employee"
    },
    {
      "label": "Phone",
      "field_name": "phones[0].phone_number",
      "data_column": "phones",
      "type": "tel",
      "type_id": 1,
      "order_index": 3,
      "child_table": "user_phones",
      "child_field": "phone_number",
      "child_defaults": {"is_primary": true},
      "help_text": "Primary phone number"
    },
    {
      "label": "Location",
      "field_name": "branch_memberships[0].branch_id",
      "data_column": "branch_memberships",
      "type": "select",
      "type_id": 4,
      "order_index": 4,
      "required": true,
      "child_table": "user_branch_memberships",
      "child_field": "branch_id",
      "child_defaults": {"role": "member"},
      "options_table": "branches",
      "options_label_column": "name",
      "options_value_column": "branch_id",
      "help_text": "Select the branch where this employee will work"
    },
    {
      "label": "Groups",
      "field_name": "group_memberships",
      "data_column": "group_memberships",
      "type": "select",
      "type_id": 4,
      "order_index": 5,
      "multiple": true,
      "options_table": "groups",
      "options_label_column": "name",
      "options_value_column": "group_id",
      "help_text": "Select groups for this employee (optional)"
    },
    {
      "label": "Password",
      "field_name": "password.password_hash",
      "data_column": "password",
      "type": "password",
      "order_index": 6,
      "required": false,
      "child_table": "user_passwords",
      "child_field": "password_hash",
      "child_defaults": {"algo": "bcrypt"},
      "help_text": "Leave blank to keep current password. Minimum 8 characters if changing."
    }
  ]$form$::jsonb
WHERE name = 'Employee Account';

-- Verify the update
SELECT 
  name,
  jsonb_pretty(elements) as form_elements
FROM forms
WHERE name = 'Employee Account';
