CREATE OR REPLACE VIEW public.tms_v_terminal_report
AS SELECT tt.sn,
    tt.imei,
    tte.terminal_id,
    tte.merchant_id,
    tte.merchant_name1,
    tte.merchant_name2,
    tte.merchant_name3,
    tte.feature_sale,
    tte.feature_sale_tip,
    tte.feature_sale_redemption,
    tte.feature_card_verification,
    tte.feature_sale_completion,
    tte.feature_installment,
    tte.feature_sale_fare_non_fare,
    tte.feature_manual_key_in,
    tte.feature_qris,
    tte.feature_contactless,
    tte.random_pin_keypad,
    tte.beep_pin_keypad,
    tte.auto_logon,
    tte.next_logon,
    tte.installment1_options,
    tte.installment2_options,
    tte.installment3_options,
    CASE
        WHEN tt.heartbeat_status = 3 THEN 'DISCONNECTED'::text
        WHEN tt.heartbeat_status = 2 THEN 'OFFLINE'::text
        WHEN tt.heartbeat_status = 1 THEN 'ONLINE'::text
        ELSE 'DISCONNECTED'::text
    END AS state,
    tt.app_version,
    tt.launcher_version,
    tt.vfs_version,
    tt.vfss_version,
    tt.update_ts,
    tt.last_diagnostic_time,
    tt.last_heartbeat_time,
    tt.latitude,
    tt.longitude,
    tt.cell_name,
    tt.cell_type,
    tt.cell_strength,
    tt.wifi_name,
    tt.wifi_strength,
    tte.push_logon,
    tte.host_report,
    tte.host_logging,
    diag.device_info_ext::json ->> 'romVer'::text AS rom_version,
    diag.device_info_ext::json ->> 'SPVer'::text AS sp_version,
    diag.installed_apps_string,
    diag.sam_available,
    tte.feature_sale_nfc,
    tte.feature_refund_nfc,
    tte.feature_sale_with_bripoin,
    tte.feature_release_card_ver,
    tte.feature_void,
    tte.feature_settlement,
    tte.feature_reprint,
    tte.feature_report,
    tte.feature_qris_generate,
    tte.feature_qris_pay,
    tte.feature_qris_refund,
    tte.feature_qris_report,
    tte.feature_brizzi_info,
    tte.feature_brizzi_info_deposit,
    tte.feature_brizzi_update_deposit,
    tte.feature_brizzi_topup,
    tte.feature_brizzi_topup_deposit,
    tte.feature_brizzi_sale,
    tte.feature_brizzi_settlement,
    tte.feature_brizzi_void,
    tte.feature_brizzi_init,
    tte.feature_brizzi_card_info,
    tte.feature_brizzi_log,
    tte.feature_brizzi_reprint,
    tte.feature_brizzi_report,
    tte.feature_re_eng_qris,
    tte.feature_contactless_qris_tap,
    diag.device_info_ext::json ->> 'firmwareVer'::text AS firmware_version,
    diag.device_info_ext::json ->> 'hardwareVer'::text AS hardware_version,
    diag.device_info_ext::json ->> 'androidOsVer'::text AS android_os_version,
    diag.device_info_ext::json ->> 'deviceModel'::text AS device_model,
    diag.device_info_ext::json ->> 'VFSerivceVer'::text AS vf_service_version,
    app_versions.ecr_version,
    app_versions.utms_version,
    app_versions.vf_service_app_version,
    app_versions.vf_system_service_version,
    app_versions.bit_sdk_version,
    app_versions.fms_bri_version
   FROM tms_terminal tt
     JOIN tms_merchant tm ON tm.id = tt.merchant_id
     JOIN tms_device_profile tdp ON tdp.id = tt.profile_id
     JOIN tms_terminal_link ttl ON ttl.terminal_id = tt.id
     JOIN tmsext_terminal_ext tte ON ttl.terminal_ext_id = tte.id AND tte.delete_ts IS NULL
     LEFT JOIN tms_diagnostic_info diag ON tt.last_diagnostic_id = diag.id AND diag.create_ts <= now()
     LEFT JOIN LATERAL (
        SELECT max(app_item ->> 'version'::text) FILTER (WHERE app_item ->> 'package_name'::text = 'com.vfi.id.ecr') AS ecr_version,
            max(app_item ->> 'version'::text) FILTER (WHERE app_item ->> 'package_name'::text = 'com.unified.launcher') AS utms_version,
            max(app_item ->> 'version'::text) FILTER (WHERE app_item ->> 'package_name'::text = 'com.vfi.smartpos.deviceservice') AS vf_service_app_version,
            max(app_item ->> 'version'::text) FILTER (WHERE app_item ->> 'package_name'::text = 'com.vfi.smartpos.system_service') AS vf_system_service_version,
            max(app_item ->> 'version'::text) FILTER (WHERE app_item ->> 'package_name'::text = 'id.bitcorp.middlewareservice') AS bit_sdk_version,
            max(app_item ->> 'version'::text) FILTER (WHERE app_item ->> 'package_name'::text = 'com.briit.brimobile') AS fms_bri_version
        FROM json_array_elements(COALESCE(NULLIF(diag.installed_apps_string, ''), '[]')::json) app_item
     ) app_versions ON true
  WHERE tt.delete_ts IS NULL;