opportunities_audit2

SELECT
  o.id AS opportunity_id,
  DATE_ADD(a.date_created, INTERVAL 3 HOUR) AS date_created,

  (SELECT COALESCE(CONCAT(last_name, ' ', first_name), user_name) FROM users WHERE id = a.created_by AND deleted = 0) AS created_user_name,
  (SELECT role_op FROM users WHERE id = a.created_by AND deleted = 0) AS created_user_role,

  a.field_name,

  IF(
    a.field_name = 'assigned_user_id',
    (SELECT COALESCE(CONCAT(last_name, ' ', first_name),user_name) FROM users WHERE id = a.before_value_string AND deleted = 0),
    a.before_value_string
  ) AS before_value,
  IF(
    a.field_name = 'assigned_user_id',
    (SELECT role_op FROM users WHERE id = a.before_value_string AND deleted = 0), NULL
  ) AS before_user_role,
  IF (
    a.field_name = 'assigned_user_id',
    (SELECT department FROM users WHERE id = a.before_value_string AND deleted = 0), NULL
  ) AS before_user_department,

  IF(
    a.field_name = 'assigned_user_id',
    (SELECT COALESCE(CONCAT(last_name, ' ', first_name),user_name) FROM users WHERE id = a.after_value_string AND deleted = 0),
    a.after_value_string
  ) AS after_value,
  IF(
    a.field_name = 'assigned_user_id',
    (SELECT role_op FROM users WHERE id = a.after_value_string AND deleted = 0), NULL
  ) AS after_user_role,
  IF (
    a.field_name = 'assigned_user_id',
    (SELECT department FROM users WHERE id = a.after_value_string AND deleted = 0), NULL
  ) AS after_user_department

FROM opportunities_audit AS a
INNER JOIN opportunities AS o ON o.id = a.parent_id
  AND o.deleted = 0
  AND o.date_modified > '2025-01-01 00:00:00'
  AND a.field_name IN ('assigned_user_id', 'sales_stage')
  WHERE
          a.created_by IN (
      SELECT id FROM users WHERE user_name = 'integration' AND deleted = 0
      UNION
      SELECT id FROM users WHERE department = 'Отдел продаж' and deleted = 0
    )