# frozen_string_literal: true

module MalawiHivProgramReports
  module Cohort
    class Outcomes
      include MalawiHivProgramReports::Adapters::Moh::Custom
      include MalawiHivProgramReports::Utils::ModelUtils
      attr_reader :end_date

      def initialize(end_date:, definition: 'moh', location: nil)
        definition = definition.downcase
        raise ::ArgumentError, "Invalid outcomes definition: #{definition}" unless %w[moh pepfar].include?(definition)

        @adapter = ActiveRecord::Base.connection.adapter_name.downcase
        @end_date = end_date.to_date
        @definition = definition
        @location = location
      end

      def update_cummulative_outcomes
        initialize_table

        # HIC SUNT DRACONIS: The order of the operations below matters,
        # do not change it unless you know what you are doing!!!
        load_patients_who_died
        load_patients_who_stopped_treatment
        load_patients_on_pre_art
        load_patients_without_state
        load_patients_without_drug_orders
        load_patients_on_treatment
        load_defaulters
      end

      private

      def arv_drugs_concept_set
        @arv_drugs_concept_set ||= ::ConceptSet.where(set: concept_name('Antiretroviral drugs').concept)
                                               .select(:concept_id)
      end

      def drug_order_type
        @drug_order_type ||= order_type('Drug order')
      end

      def program_states(*names)
        ::ProgramWorkflowState.joins(:program_workflow)
                              .joins(:concept)
                              .merge(::ProgramWorkflow.where(program: hiv_program))
                              .merge(::Concept.joins(:concept_names)
                                          .merge(::ConceptName.where(name: names)))
                              .select(:program_workflow_state_id)
      end

      def hiv_program
        @hiv_program ||= program('HIV Program')
      end

      def initialize_table
        ActiveRecord::Base.connection.execute <<~SQL
          DROP TABLE IF EXISTS temp_patient_outcomes
        SQL

        ActiveRecord::Base.connection.execute <<~SQL
          CREATE TABLE temp_patient_outcomes (
            patient_id INT NOT NULL,
            cum_outcome VARCHAR(120) NOT NULL,
            outcome_date DATE DEFAULT NULL,
            site_id INT NOT NULL,
            PRIMARY KEY (patient_id, site_id)
          )
        SQL

        ActiveRecord::Base.connection.execute <<~SQL
          CREATE INDEX tpo_outcomes ON temp_patient_outcomes (patient_id, cum_outcome, outcome_date, site_id)
        SQL
      end

      # Loads all patiens with an outcome of died as of given date
      # into the temp_patient_outcomes table.
      def load_patients_who_died
        date = ActiveRecord::Base.connection.quote(end_date)

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_patient_outcomes
          SELECT patients.patient_id, 'Patient died', patient_state.start_date, #{@location}
          FROM temp_earliest_start_date AS patients
          INNER JOIN patient_program
            ON patient_program.patient_id = patients.patient_id
            AND patient_program.program_id = #{hiv_program.program_id}
            AND patient_program.voided = 0 #{site_manager(operator: 'AND', column: 'patient_program.site_id', location: @location)}
          INNER JOIN patient_state
            ON patient_state.patient_program_id = patient_program.patient_program_id
            AND patient_state.state = (#{program_states('Patient died').limit(1).to_sql})
            AND patient_state.start_date < #{interval_manager(date:, value: 1, interval: 'DAY', operator: '+')}
            AND patient_state.voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
          WHERE patients.date_enrolled <= #{date} #{site_manager(operator: 'AND', column: 'patients.site_id', location: @location)}
          AND patient_state.date_created = (SELECT MAX(date_created)
            FROM patient_state ps
            WHERE ps.patient_program_id = patient_state.patient_program_id #{site_manager(operator: 'AND', column: 'ps.site_id', location: @location)}
            AND ps.state = patient_state.state AND ps.voided = 0 AND ps.start_date <= #{date})
          GROUP BY patients.patient_id
        SQL
      end

      # Loads all patients with an outcome of transferred out or
      # treatment stopped into temp_patient_outcomes table.
      def load_patients_who_stopped_treatment
        date = ActiveRecord::Base.connection.quote(end_date)

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_patient_outcomes
          SELECT patients.patient_id,
                 (
                   SELECT name FROM concept_name
                   WHERE concept_id = (
                   SELECT concept_id FROM program_workflow_state
                   WHERE program_workflow_state_id = patient_state.state
                   LIMIT 1
                   )
                 ) AS cum_outcome,
                 patient_state.start_date, #{@location}
          FROM temp_earliest_start_date AS patients
          INNER JOIN patient_program
            ON patient_program.patient_id = patients.patient_id
            AND patient_program.program_id = #{hiv_program.program_id}
            AND patient_program.voided = 0 #{site_manager(operator: 'AND', column: 'patient_program.site_id', location: @location)}
          INNER JOIN patient_state
            ON patient_state.patient_program_id = patient_program.patient_program_id
            AND patient_state.state IN (#{program_states('Patient transferred out', 'Treatment stopped').to_sql})
            AND patient_state.start_date < #{interval_manager(date: end_date, value: 1, interval: 'DAY', operator: '+')}
            AND (patient_state.end_date >= #{date} OR patient_state.end_date IS NULL)
            AND patient_state.voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
          INNER JOIN (
            SELECT patient_program_id, MAX(start_date) AS start_date
            FROM patient_state
            WHERE patient_state.start_date < #{interval_manager(date: end_date, value: 1, interval: 'DAY', operator: '+')}
              AND patient_state.voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
            GROUP BY patient_program_id
          ) AS max_patient_state
            ON max_patient_state.patient_program_id = patient_state.patient_program_id
            AND max_patient_state.start_date = patient_state.start_date #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
          WHERE patients.date_enrolled <= #{date} #{site_manager(operator: 'AND', column: 'patients.site_id', location: @location)}
            AND patients.patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)} )
          GROUP BY patients.patient_id #{@adapter == 'mysql2' ? '' : ',patient_state.state, patient_state.start_date'}
        SQL
      end

      # Load all patients on Pre-ART.
      def load_patients_on_pre_art
        date = ActiveRecord::Base.connection.quote(end_date)

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_patient_outcomes
          SELECT patients.patient_id,
                CASE
                  WHEN #{current_defaulter_function('patients.patient_id')} = 1 THEN 'Defaulted'
                  ELSE 'Pre-ART (Continue)'
                END AS cum_outcome,
                patient_state.start_date, #{@location}
          FROM temp_earliest_start_date AS patients
          INNER JOIN patient_program
            ON patient_program.patient_id = patients.patient_id
            AND patient_program.program_id = #{hiv_program.program_id}
            AND patient_program.voided = 0 #{site_manager(operator: 'AND', column: 'patient_program.site_id', location: @location)}
          INNER JOIN patient_state
            ON patient_state.patient_program_id = patient_program.patient_program_id
            AND patient_state.state = (#{program_states('Pre-ART (Continue)').limit(1).to_sql})
            AND patient_state.start_date < #{interval_manager(date: end_date, value: 1, interval: 'DAY', operator: '+')}
            AND (patient_state.end_date >= #{date} OR patient_state.end_date IS NULL)
            AND patient_state.voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
          INNER JOIN (
            SELECT patient_program_id, MAX(start_date) AS start_date
            FROM patient_state
            WHERE patient_state.start_date < #{interval_manager(date: end_date, value: 1, interval: 'DAY', operator: '+')}
              AND patient_state.voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
            GROUP BY patient_program_id
          ) AS max_patient_state
            ON max_patient_state.patient_program_id = patient_state.patient_program_id
            AND max_patient_state.start_date = patient_state.start_date #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
          WHERE patients.date_enrolled <= #{date} #{site_manager(operator: 'AND', column: 'patients.site_id', location: @location)}
            AND patients.patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)} )
          GROUP BY patients.patient_id #{@adapter == 'mysql2' ? '' : ',patient_state.start_date'}
        SQL
      end

      # Load all patients without a state
      def load_patients_without_state
        date = ActiveRecord::Base.connection.quote(end_date)

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_patient_outcomes
          SELECT patients.patient_id,
                 CASE
                    WHEN #{current_defaulter_function('patients.patient_id')} = 1 THEN 'Defaulted'
                    ELSE 'Unknown'
                  END AS cum_outcome,
                 NULL, #{@location} as site_id
          FROM temp_earliest_start_date AS patients
          INNER JOIN patient_program
            ON patient_program.patient_id = patients.patient_id
            AND patient_program.program_id = #{hiv_program.program_id}
            AND patient_program.voided = 0 #{site_manager(operator: 'AND', column: 'patient_program.site_id', location: @location)}
          WHERE patients.date_enrolled <= #{date}
            AND patient_program.patient_program_id NOT IN (
              SELECT patient_program_id
              FROM patient_state
              WHERE start_date < #{interval_manager(date: end_date, value: 1, interval: 'DAY', operator: '+')}
                AND voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
            )
            AND patients.patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'patient_program.site_id', location: @location)} )
          GROUP BY patients.patient_id
        SQL
      end

      # Load all patients without drug orders or have drug orders
      # without a quantity.
      def load_patients_without_drug_orders
        date = ActiveRecord::Base.connection.quote(end_date)

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_patient_outcomes
          SELECT patients.patient_id,
                 'Unknown',
                 NULL, #{@location}
          FROM temp_earliest_start_date AS patients
          WHERE date_enrolled <= #{date} #{site_manager(operator: 'AND', column: 'patients.site_id', location: @location)}
            AND patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)} )
            AND patient_id NOT IN (
              SELECT patient_id
              FROM orders
              LEFT JOIN drug_order ON orders.order_id = drug_order.order_id #{site_manager(operator: 'AND', column: 'drug_order.site_id', location: @location)}
              WHERE start_date < #{interval_manager(date: end_date, value: 1, interval: 'DAY', operator: '+')}
                AND quantity > 0 #{site_manager(operator: 'AND', column: 'orders.site_id', location: @location)}
                AND order_type_id = #{drug_order_type.order_type_id}
                AND concept_id IN (#{arv_drugs_concept_set.to_sql})
            )
        SQL
      end

      # Loads all patients who are on treatment
      def load_patients_on_treatment
        date = ActiveRecord::Base.connection.quote(end_date)

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_patient_outcomes
          SELECT patients.patient_id, 'On antiretrovirals', patient_state.start_date, #{@location}
          FROM temp_earliest_start_date AS patients
          INNER JOIN patient_program
            ON patient_program.patient_id = patients.patient_id
            AND patient_program.program_id = #{hiv_program.program_id}
            AND patient_program.voided = 0 #{site_manager(operator: 'AND', column: 'patient_program.site_id', location: @location)}
          /* Get patients' `on ARV` states that are before given date */
          INNER JOIN patient_state
            ON patient_state.patient_program_id = patient_program.patient_program_id
            AND patient_state.state = (#{program_states('On antiretrovirals').limit(1).to_sql})
            AND patient_state.start_date < #{interval_manager(date:, value: 1, interval: 'DAY', operator: '+')}
            AND (patient_state.end_date >= #{date} OR patient_state.end_date IS NULL)
            AND patient_state.voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
          /* Select only the most recent state out of those retrieved above */
          INNER JOIN (
            SELECT patient_program_id, MAX(start_date) AS start_date
            FROM patient_state
            WHERE patient_state.start_date < #{interval_manager(date:, value: 1, interval: 'DAY', operator: '+')}
              AND patient_state.voided = 0 #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
            GROUP BY patient_program_id
          ) AS max_patient_state
            ON max_patient_state.patient_program_id = patient_state.patient_program_id
            AND max_patient_state.start_date = patient_state.start_date #{site_manager(operator: 'AND', column: 'patient_state.site_id', location: @location)}
          /* HACK: Ensure that the states captured above do correspond have corresponding
                   ARV dispensations. In other words filter out any `on ARVs` states whose
                   dispensation's may have been voided or states that were created manually
                   without any drugs being dispensed.  */
          INNER JOIN (
            SELECT orders.patient_id, MIN(orders.auto_expire_date) AS auto_expire_date
            FROM orders
            INNER JOIN drug_order ON orders.order_id = drug_order.order_id #{site_manager(operator: 'AND', column: 'drug_order.site_id', location: @location)}
            INNER JOIN (
              SELECT patient_id, MAX(start_date) AS start_date
              FROM orders
              INNER JOIN drug_order ON orders.order_id = drug_order.order_id #{site_manager(operator: 'AND', column: 'drug_order.site_id', location: @location)} AND drug_order.quantity > 0
              WHERE order_type_id = #{drug_order_type.order_type_id}
                AND concept_id IN (#{arv_drugs_concept_set.to_sql})
                AND orders.start_date < #{interval_manager(date:, value: 1, interval: 'DAY', operator: '+')}
                AND voided = 0 #{site_manager(operator: 'AND', column: 'orders.site_id', location: @location)}
                AND patient_id IN (SELECT patient_id FROM temp_earliest_start_date #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)} )
                AND patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)} )
              GROUP BY patient_id
            ) AS max_drug_orders
              ON max_drug_orders.patient_id = orders.patient_id
              AND max_drug_orders.start_date = orders.start_date #{site_manager(operator: 'AND', column: 'orders.site_id', location: @location)}
            WHERE order_type_id = #{drug_order_type.order_type_id}
              AND concept_id IN (#{arv_drugs_concept_set.to_sql})
              AND orders.start_date < #{interval_manager(date:, value: 1, interval: 'DAY', operator: '+')}
              AND quantity > 0
              AND voided = 0 #{site_manager(operator: 'AND', column: 'orders.site_id', location: @location)}
              AND orders.patient_id IN (SELECT patient_id FROM temp_earliest_start_date #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)})
              AND orders.patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)})
            GROUP BY orders.patient_id
          ) AS first_order_to_expire
            ON (first_order_to_expire.auto_expire_date >= #{date} OR #{timestampdiff_manager(date1: 'DATE(first_order_to_expire.auto_expire_date)', date2: date, interval: 'DAY')} <= #{@definition == 'pepfar' ? 28 : 56})
            AND first_order_to_expire.patient_id = patient_program.patient_id #{site_manager(operator: 'AND', column: 'patient_program.site_id', location: @location)}
          WHERE patients.date_enrolled <= #{date} #{site_manager(operator: 'AND', column: 'patients.site_id', location: @location)}
            AND patients.patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)} )
          GROUP BY patients.patient_id #{@adapter == 'mysql2' ? '' : ',patient_state.start_date'}
        SQL
      end

      # Load defaulters
      def load_defaulters
        date = ActiveRecord::Base.connection.quote(end_date)

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_patient_outcomes
          SELECT patient_id, #{patient_outcome_function('patient_id')}, NULL, #{@location}
          FROM temp_earliest_start_date
          WHERE date_enrolled <= #{date} #{site_manager(operator: 'AND', column: 'site_id', location: @location)}
            AND patient_id NOT IN (SELECT patient_id FROM temp_patient_outcomes #{site_manager(operator: 'WHERE', column: 'site_id', location: @location)} )
        SQL
      end

      def current_defaulter_function(sql_column)
        defaulter_function = @definition == 'moh' ? 'current_defaulter' : 'current_pepfar_defaulter'
        quoted_end_date = ActiveRecord::Base.connection.quote(end_date)
        parameters = "#{sql_column}, #{quoted_end_date}, #{@location}"
        function_manager(function: defaulter_function, location: @location, args: parameters)
      rescue StandardError => e
        Rails.logger.error "Error: #{e.message}"
        raise "Invalid outcomes definition: #{@definition}"
      end

      def patient_outcome_function(sql_column)
        outcome_function = @definition == 'moh' ? 'patient_outcome' : 'pepfar_patient_outcome'
        quoted_end_date = ActiveRecord::Base.connection.quote(end_date)
        parameters = "#{sql_column}, #{quoted_end_date}, #{@location}"
        function_manager(function: outcome_function, location: @location, args: parameters)
      rescue StandardError => e
        Rails.logger.error "Error: #{e.message}"
        raise "Invalid outcomes definition: #{@definition}"
      end
    end
  end
end