create function sp_patient_problem_create(p_patient_problem_id uuid, p_patient_id uuid, p_patient_visit_id uuid, p_list_json_icd json, p_symptom character varying, p_treatment_plans_rcd character varying, p_treatment_flag boolean, p_recommendation character varying, p_diagnose_result text, p_catastrophe_flag boolean, p_complication_flag boolean, p_dissection_rcd character varying, p_treatment_result_rcd character varying, p_json_object_die json, p_cpoe_placer_order_id uuid, p_icd_free_text description, p_list_json_cpoe_placer_order_exploding json, p_created_by_user_id uuid, p_created_by_department_id uuid) returns character varying
    language plpgsql
as
$$
/*
  DROP FUNCTION sp_patient_problem_create;
  TODO check flag update visit
*/
DECLARE
  v_patient_problem_id  UUID;
  v_processing_queue_id UUID;
  v_clinic_room_id      UUID;

  v_not_allow_main_icd  CHARACTER VARYING :='H52.0,H52.1,H52.2,H52.4';
  v_err_context         TEXT;
BEGIN
  IF v_not_allow_main_icd NOTNULL AND p_list_json_icd NOTNULL
  THEN
    IF exists(SELECT 1
              FROM json_populate_recordset(NULL :: ICD_REF, p_list_json_icd) AS rec
              WHERE rec.seq_num = 0 AND rec.icd_code = ANY (string_to_array(v_not_allow_main_icd,
                                                                            ',') :: CHARACTER VARYING []))
    THEN
      RAISE EXCEPTION 'MESSAGE.icd_main_not_allow';
    END IF;
  END IF;

  SELECT
    processing_queue_id,
    coalesce(clinic_room_id, facility_id)
  INTO v_processing_queue_id, v_clinic_room_id
  FROM patient_visit
  WHERE patient_visit_id = p_patient_visit_id;

  v_patient_problem_id = coalesce(p_patient_problem_id, uuid_generate_v4());
  IF exists(SELECT 1
            FROM patient_problem pp
            WHERE pp.patient_visit_id = p_patient_visit_id AND (pp.active_flag = 1 OR pp.active_flag = 0))
  THEN
    UPDATE patient_problem pp
    SET list_json_icd      = p_list_json_icd,
      symptom              = p_symptom,
      treatment_plans_rcd  = p_treatment_plans_rcd,
      treatment_flag       = p_treatment_flag,
      recommendation       = p_recommendation,
      cpoe_placer_order_id = p_cpoe_placer_order_id,
      diagnose_result      = p_diagnose_result,
      icd_free_text        = p_icd_free_text,
      lu_updated           = now() :: DATETIME,
      lu_user_id           = p_created_by_user_id,
      active_flag          = 1
    WHERE pp.patient_visit_id = p_patient_visit_id;
  ELSE
    RAISE LOG 'INSERT new p_patient_problem_id %', v_patient_problem_id;
    INSERT INTO patient_problem (patient_problem_id, patient_id, patient_visit_id, list_json_icd, symptom, treatment_plans_rcd, treatment_flag,
                                 recommendation, cpoe_placer_order_id, created_by_user_id, examination_date, diagnose_result, icd_free_text, created_date_time, active_flag, lu_updated, lu_user_id)
    VALUES
      (v_patient_problem_id, p_patient_id, p_patient_visit_id, p_list_json_icd, p_symptom, p_treatment_plans_rcd,
                             p_treatment_flag, p_recommendation, p_cpoe_placer_order_id, p_created_by_user_id, now(),
       p_diagnose_result, p_icd_free_text, now(), 1, now(), p_created_by_user_id)
    ON CONFLICT (patient_visit_id)
      DO UPDATE SET active_flag = 1;
  END IF;

  --update cpoe status
  UPDATE cpoe_placer_order cpo
  SET lu_user_id                 = p_created_by_user_id,
    lu_updated                   = now(),
    cpoe_placer_order_status_rcd = 'COMPLETED',
    administration_date_time     = now(),
    administration_user_id       = p_created_by_user_id,
    provider_id                  = p_created_by_department_id
  WHERE cpo.active_flag = 1 AND cpo.cpoe_placer_order_id = p_cpoe_placer_order_id;

  --update done examination not allow cancel charge detail
  UPDATE charge_detail
  SET lu_updated              = now(),
    lu_user_id                = p_created_by_user_id,
    allow_cancel_flag         = FALSE,
    remain_quantity           = 0,
    executed_from_area_id     = v_clinic_room_id,
    executed_by_employee_id   = p_created_by_user_id,
    executed_by_department_id = p_created_by_department_id,
    executed_datetime         = now(),
    completed_datetime        = now()
  WHERE active_flag = 1 AND cpoe_placer_order_id = p_cpoe_placer_order_id;
  /*
  --revenue summary
  SELECT sp_charge_detail_summary_day_cru(p_cpoe_placer_order_id, --p_cpoe_placer_order_id    UUID,
                                          1, --p_quantity QUANTITY,
                                          p_created_by_user_id, --p_executed_by_employee_id UUID,
                                          p_created_by_department_id, --p_executed_by_department_id UUID,
                                          p_created_by_user_id--p_created_by_user_id      UUID)
  )
  INTO v_result;
  IF v_result <> ''
  THEN
    RAISE EXCEPTION '%', v_result;
  END IF;
  */
  --update cpoe exploding
  IF p_list_json_cpoe_placer_order_exploding NOTNULL
  THEN
    UPDATE cpoe_placer_order_exploding cpoe
    SET lu_user_id    = p_created_by_user_id, lu_updated = now(),
      supply_quantity = rec.supply_quantity
    FROM json_populate_recordset(NULL :: CPOE_PLACER_ORDER_EXPLODING, p_list_json_cpoe_placer_order_exploding) AS rec
    WHERE cpoe.active_flag = 1 AND cpoe.default_booking = FALSE AND
          cpoe.cpoe_placer_order_exploding_id = rec.cpoe_placer_order_exploding_id;
  END IF;
  --update cancel visit
  UPDATE patient_visit
  SET allow_cancel_flag = FALSE, allow_cancel_service_flag = FALSE, lu_updated = now(),
    lu_user_id          = p_created_by_user_id
  WHERE patient_visit_id = p_patient_visit_id;

  UPDATE patient_visit_history
  SET allow_cancel_flag = FALSE, allow_cancel_service_flag = FALSE, lu_updated = now(),
    lu_user_id          = p_created_by_user_id
  WHERE patient_visit_id = p_patient_visit_id;

  UPDATE processing_queue
  SET
    -- active_flag = 3,
    lu_updated = now(),
    lu_user_id = p_created_by_user_id
  WHERE processing_queue_id = v_processing_queue_id;

  --patient die
  /*IF p_json_object_die NOTNULLsp_charge_type_ref_get_list_dropdown
  THEN
    SELECT sp_patient_die_cru(p_patient_visit_id, p_patient_id, p_json_object_die, p_created_by_user_id)
    INTO v_result;
  END IF;
  --patient visit
  UPDATE patient_visit
  SET catastrophe_flag    = p_catastrophe_flag,
    complication_flag     = p_complication_flag,
    dissection_rcd        = CASE WHEN p_dissection_rcd NOTNULL AND p_dissection_rcd <> ''
      THEN p_dissection_rcd
                            ELSE dissection_rcd END,
    treatment_result_rcd = CASE WHEN p_treatment_result_rcd NOTNULL AND p_treatment_result_rcd <> ''
      THEN p_treatment_result_rcd
                            ELSE treatment_result_rcd END,
    lu_updated            = now(),
    lu_user_id            = p_created_by_user_id
  WHERE patient_visit_id = p_patient_visit_id;*/

  RETURN '';
  EXCEPTION WHEN OTHERS
  THEN
    BEGIN
      GET STACKED DIAGNOSTICS v_err_context = PG_EXCEPTION_CONTEXT;
      RAISE LOG '##### [sp_patient_problem_create] Error Name: %', SQLERRM;
      RAISE LOG '##### [sp_patient_problem_create] Error State: %', SQLSTATE;
      RAISE LOG '##### [sp_patient_problem_create] Error Context: %', v_err_context;
      RETURN SQLERRM;
    END;
END;
$$;

alter function sp_patient_problem_create(uuid, uuid, uuid, json, varchar, varchar, boolean, varchar, text, boolean, boolean, varchar, varchar, json, uuid, description, json, uuid, uuid) owner to postgres;