defmodule Api.Study do
  import Ecto.Query
  alias Api.Repo
  require Logger

  def open_study(accession) do
    Envar.load(".env")
    Envar.require_env_file(".env")

    idsite = Envar.get("IDSITE") |> String.to_integer
    domain = Envar.get("CHECK_ORIGIN")

    id_study = if Envar.get("IDENTIFIERFIELD") == "ACCESSIONNUMBER" do
      query =
      from s in "study",
      where: s.accessionnumber == ^accession,
      select: s.idstudy
      idstudy = Repo.one(query)
      Logger.info("accession ----> #{inspect(accession)} - idstudy -------> #{inspect(idstudy)}")
      idstudy
    else
      accession
    end

    id_study = if is_integer(id_study), do: id_study, else: String.to_integer(id_study)

    Logger.info("id_study -------> #{inspect(id_study)}")

    # Las siguientes consultas obtienen la información completa
    # de un estudio.

    # informes
    subquery1 = subquery(
      from s in "study",
        join: sr in "studyreport", on: sr.idstudy == s.idstudy,
        join: p in "patient", on: s.idpatient == p.idpatient,
        where: not is_nil(sr.body) and s.idstudy == ^id_study and
               sr.idstatus in subquery(
                 from st in "statuses",
                   where: st.statusname in ["Revisado", "Final"],
                   select: st.idstatus
               ),
        select: %{
          idsite: type(^idsite, :integer),
          iddocument: fragment("substring(encrypt(?::text::bytea, '1nf0rm3', 'aes')::text from 3)", sr.idstudyreport),
          document_name: "INFORME PRINCIPAL",
          document_type: "pdf",
          url: fragment(
            "concat(?::text, ?::text, substring(encrypt(?::text::bytea, '1nf0rm3', 'aes')::text from 3))",
            ^domain,
            "/api/downloadpdf/",
            sr.idstudyreport
          ),
          patientname: p.patientname,
          proceduredescription: s.studydescription,
          studydate: fragment("TO_CHAR(?, 'YYYY-MM-DD')", s.studydate),
          studytime: fragment("TO_CHAR(?, 'HH24:MI:SS')", s.studytime),
          accessionnumber: s.idstudy,
          patientid: p.patientid
        }
    )


    # escaneados
    escaneados_clave = Envar.get("ESCANEADOS_CLAVE")
    base_escaneados = Envar.get("ESCANEADOS")

    escaneados_expr =
      if escaneados_clave do
        dynamic([_, ss, _, _],
          fragment(
            "concat(?::text, substring(encrypt(?::text::bytea, ?, 'aes')::text from 3))",
            ^base_escaneados,
            ss.idstudyscan,
            ^escaneados_clave
          )
        )
      else
        dynamic([_, ss, _, _],
          fragment(
            "concat(?::text, ?::text)",
            ^base_escaneados,
            ss.idstudyscan
          )
        )
      end

    select_expr_es =
      dynamic([s, ss, sc, p], %{
        idsite: type(^idsite, :integer),
          iddocument: fragment("substring(encrypt(?::text::bytea, '1nf0rm3', 'aes')::text from 3)", ss.idstudyscan),
          document_name: sc.scanclass,
          document_type: "url",
          url: ^escaneados_expr,
          patientname: p.patientname,
          proceduredescription: s.studydescription,
          studydate: fragment("TO_CHAR(?, 'YYYY-MM-DD')", s.studydate),
          studytime: fragment("TO_CHAR(?, 'HH24:MI:SS')", s.studytime),
          accessionnumber: s.idstudy,
          patientid: p.patientid
      })

    subquery2 = subquery(
      from s in "study",
        join: ss in "studyscans", on: ss.idstudy == s.idstudy,
        join: sc in "scanclasses", on: sc.idscanclass == ss.idscanclass,
        join: p in "patient", on: s.idpatient == p.idpatient,
        where: s.idstudy == ^id_study,
        select: ^select_expr_es
    )

    # adjuntos
    adjuntos_clave = Envar.get("ADJUNTOS_CLAVE")
    base_adjuntos = Envar.get("ADJUNTOS")

    adjuntos_expr =
      if adjuntos_clave do
        dynamic([_, sa, _],
          fragment(
            "concat(?::text, substring(encrypt(?::text::bytea, ?, 'aes')::text from 3))",
            ^base_adjuntos,
            sa.idstudyattachment,
            ^adjuntos_clave
          )
        )
      else
        dynamic([_, sa, _],
          fragment(
            "concat(?::text, ?::text)",
            ^base_adjuntos,
            sa.idstudyattachment
          )
        )
      end

    select_expr_ad =
      dynamic([s, sa, p], %{
        idsite: type(^idsite, :integer),
        iddocument:
          fragment(
            "substring(encrypt(?::text::bytea, '1nf0rm3', 'aes')::text from 3)",
            sa.idstudyattachment
          ),
        document_name: sa.name,
        document_type:
          fragment(
            "CASE WHEN ? NOT IN ('jpg', 'jpeg', 'png') THEN 'attachment' ELSE 'url' END",
            sa.format
          ),
        url: ^adjuntos_expr,
        patientname: p.patientname,
        proceduredescription: s.studydescription,
        studydate: fragment("TO_CHAR(?, 'YYYY-MM-DD')", s.studydate),
        studytime: fragment("TO_CHAR(?, 'HH24:MI:SS')", s.studytime),
        accessionnumber: s.idstudy,
        patientid: ""
      })

    subquery3 = subquery(
      from s in "study",
        join: sa in "studyattachments", on: sa.idstudy == s.idstudy,
        join: p in "patient", on: p.idpatient == s.idpatient,
        where: s.idstudy == ^id_study,
        select: ^select_expr_ad
    )

    # Si no encontró informes, ni adjuntos, ni nada
    # se traen sólo los datos básicos del estudio
    basicquery =
      from s in "study",
        join: p in "patient", on: p.idpatient == s.idpatient,
        where: s.idstudy == ^id_study,
        select: %{
          idsite: type(^idsite, :integer),
          patientname: p.patientname,
          proceduredescription: s.studydescription,
          studydate: fragment("TO_CHAR(?, 'YYYY-MM-DD')", s.studydate),
          studytime: fragment("TO_CHAR(?, 'HH24:MI:SS')", s.studytime),
          accessionnumber: s.idstudy,
          patientid: ""
        }


    combined_query =
      from q in subquery1,
        union_all: ^subquery2,
        union_all: ^subquery3,
        select: q

    results = Repo.all(combined_query)
    case results do
      [] -> Repo.all(from q in basicquery)
      _ -> results
    end
  end
end