BEGIN;

-- Preserve legacy panel records while adding stable tenant-scoped user IDs
-- wherever the recorded member email uniquely identifies an application user.
WITH linked AS (
    SELECT panel.id,
           jsonb_agg(
        CASE
            WHEN matched.id IS NULL THEN member.value
            ELSE member.value || jsonb_build_object('user_id', matched.id::text)
        END
        ORDER BY member.ordinality
    ) AS members
    FROM hr_payroll.recruitment_interview_panels panel
    CROSS JOIN LATERAL jsonb_array_elements(COALESCE(panel.members, '[]'::jsonb)) WITH ORDINALITY member(value, ordinality)
    LEFT JOIN LATERAL (
        SELECT users.id
        FROM iam.users users
        WHERE users.tenant_id = panel.tenant_id
          AND NULLIF(TRIM(member.value->>'email'), '') IS NOT NULL
          AND LOWER(TRIM(users.email)) = LOWER(TRIM(member.value->>'email'))
        ORDER BY users.id
        LIMIT 1
    ) matched ON TRUE
    WHERE COALESCE(panel.is_deleted, FALSE) = FALSE
    GROUP BY panel.id
)
UPDATE hr_payroll.recruitment_interview_panels panel
SET members = linked.members,
    updated_at = CURRENT_TIMESTAMP
FROM linked
WHERE linked.id = panel.id;

WITH chairs AS (
    SELECT panel.id AS panel_id, member->>'user_id' AS user_id
    FROM hr_payroll.recruitment_interview_panels panel
    CROSS JOIN LATERAL jsonb_array_elements(COALESCE(panel.members, '[]'::jsonb)) member
    WHERE LOWER(COALESCE(member->>'chair', 'false')) = 'true'
      AND NULLIF(member->>'user_id', '') IS NOT NULL
)
UPDATE hr_payroll.recruitment_interview_panels panel
SET chair_user_id = chairs.user_id::uuid,
    updated_at = CURRENT_TIMESTAMP
FROM chairs
WHERE chairs.panel_id = panel.id
  AND panel.chair_user_id IS NULL
  AND chairs.user_id ~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$';

COMMIT;