Untitled

SELECT
  woi.person_id,
  woi.id AS work_item_id,
  woi.name AS work_item_name,
  woi.description AS work_item_description,
  woi.chat_room_id AS work_item_chat_room_id,
  woi."itemTypeId" AS item_type_id,

  wit.name AS item_type_name,          
  wit.icon AS item_type_icon,
  wit."backgroundColor" AS item_type_background_color,
  wit."personalFormsId" AS item_type_personal_form_id,
  pfo.name AS item_type_personal_form_name,

  COALESCE(
    woi."formValues"->'empreendedorId',
	woi."formValues"->'empresaDesenvolvedora'
  ) AS form_desenvolvedor_id,

  COALESCE(
    woi."formValues"->'nomeEmpreendimento',
    woi."formValues"->'nomedoempreendimento'
  ) AS form_nome_empreendimento,
  
  CASE WHEN wit."personalFormsId" = 1
    THEN 'Equity'
    ELSE (woi."formValues"->'tipoOperacao')::CHARACTER VARYING
  END form_tipo_operacao_id,

  CASE WHEN wit."personalFormsId" = 1
    THEN 'Equity'
    ELSE CASE (woi."formValues"->'tipoOperacao')::CHARACTER VARYING
      WHEN 'EQT' THEN 'Equity'
      WHEN 'B2B' THEN 'Crédito B2B'
      WHEN '1' THEN 'Crédito B2B'
      WHEN 'B2C' THEN 'Crédito B2C'
      WHEN '2' THEN 'Crédito B2C'
    END
  END AS form_tipo_operacao,

  COALESCE(
    woi."formValues"->'intermediadorId',
    woi."formValues"->'intermediador'
  ) form_intermediador_id,
  
  CASE (woi."formValues"->'vertical')::CHARACTER VARYING
    WHEN '1' THEN 'Incorporação'
    WHEN '2' THEN 'Loteamento'
    WHEN '3' THEN 'Multipropriedade'
  END AS vertical,

  woi."formValues"->'empresaIntermediador' AS form_empresa_intermediador_id,
  woi."formValues"->'cidade' AS form_cidade,
  woi."formValues"->'uf' AS form_uf,

  CASE wit."personalFormsId"
    WHEN 1 THEN /* Equity */
      CASE (
        COALESCE(
          woi."formValues"->'tipoempreendimento',
          woi."formValues"->'tipoEmpreendimento'
        )
      )::CHARACTER VARYING
        WHEN '1' THEN 'Loteamento Aberto'
        WHEN '2' THEN 'Loteamento Fechado'
        WHEN '3' THEN 'Incorporação Horizontal'
        WHEN '4' THEN 'Incorporação Vertical'
        WHEN '5' THEN 'Fracionado/Multipropriedade'
        WHEN '6' THEN 'Shopping ou Street Mall'
        WHEN '7' THEN 'Galpão Logístico'
        WHEN '8' THEN 'Rural'
        WHEN '9' THEN 'Hotel'
        WHEN '10' THEN 'Outros'
      END
    WHEN 2 THEN /* Crédito */
      CASE (
        COALESCE(
          woi."formValues"->'tipoempreendimento',
          woi."formValues"->'tipoEmpreendimento'
        )
      )::CHARACTER VARYING
        WHEN '1' THEN 'Loteamento Aberto'
        WHEN '2' THEN 'Condomínio Fechado'
        WHEN '3' THEN 'Incorporação Horizontal'
        WHEN '4' THEN 'Incorporação Vertical'
        WHEN '6' THEN 'Fracionado / Multipropriedade'
        WHEN '7' THEN 'Shopping ou Street Mall'
        WHEN '8' THEN 'Galpão Logístico'
        WHEN '9' THEN 'Rural'
        WHEN '10' THEN 'Hotel'
        WHEN '11' THEN 'Outros'
      END
    WHEN 3 THEN /* Front */
      CASE (
        COALESCE(
          woi."formValues"->'tipoempreendimento',
          woi."formValues"->'tipoEmpreendimento'
        )
      )::CHARACTER VARYING
        WHEN 'LTA' THEN 'Loteamento Aberto'
        WHEN 'LTF' THEN 'Loteamento Fechado'
        WHEN 'ICH' THEN 'Incorporação Horizontal'
        WHEN 'ICV' THEN 'Incorporação Vertical'
        WHEN 'FMP' THEN 'Fracionado / Multipropriedade'
        WHEN 'STM' THEN 'Shopping ou Street Mall'
        WHEN 'GPL' THEN 'Galpão Logístico'
        WHEN 'RRL' THEN 'Rural'
        WHEN 'HOT' THEN 'Hotel'
        WHEN 'OUT' THEN 'Outros'
      END
  END AS form_tipo_empreendimento,
  COALESCE(
    woi."formValues"->'tipoempreendimento',
    woi."formValues"->'tipoEmpreendimento'
  ) AS form_tipo_empreendimento_id,

  woi.hash_md5 AS work_item_hash_md5,
  woi.updated_at AS record_date_time,
  woi.created_at AS creation_date_time,
  
  wif.id AS work_item_father_id
FROM
  work_items woi
  
INNER JOIN work_item_types wit
ON woi."itemTypeId" = wit.id

LEFT JOIN personal_forms pfo
ON wit."personalFormsId" = pfo.id

LEFT JOIN work_items wif
ON woi."fatherId" = wif.id
AND wif.deleted = false

WHERE woi.deleted = false
AND woi.id = -- workItemId