# frozen_string_literal: true

module MalawiHivProgramReports
  module Moh
    # This class is used to add additional cohort disaggregated data
    # rubocop:disable Metrics/ClassLength
    class CohortDisaggregated
      include MalawiHivProgramReports::Utils::ModelUtils
      include MalawiHivProgramReports::Adapters::Moh::Custom

      def initialize(name:, type:, start_date:, end_date:, rebuild:, **kwargs)
        @name = name
        @type = type
        @start_date = start_date
        @end_date = end_date
        @rebuild = rebuild
        @occupation = kwargs[:occupation]
        @location = kwargs[:location]
        @adapter = ActiveRecord::Base.connection.adapter_name.downcase
      end

      def find_report
        build_report
      end

      def build_report
        builder = CohortDisaggregatedBuilder.new(outcomes_definition: 'moh', location: @location,
                                                 occupation: @occupation)
        builder.build(nil, @start_date, @end_date)
      end

      def initialize_disaggregated
        ActiveRecord::Base.connection.execute('DROP TABLE IF EXISTS temp_disaggregated')

        ActiveRecord::Base.connection.execute(
          'CREATE TABLE IF NOT EXISTS temp_disaggregated (
             patient_id INTEGER NOT NULL,
             age_group VARCHAR(20),
             initial_maternal_status VARCHAR(10),
             maternal_status VARCHAR(10),
             given_ipt INT(1),
             screened_for_tb INT(1),
             site_id INT NOT NULL DEFAULT 1,
             PRIMARY KEY(patient_id, site_id)
          );'
        )

        { temp_disaggregated: 'created' }
      end

      def disaggregated(quarter, age_group)
        if quarter == 'pepfar'
          start_date = @start_date
          end_date = @end_date

          begin
            records = ActiveRecord::Base.connection.select_one("SELECT count(*) rec_count FROM temp_patient_outcomes #{site_manager(
              operator: 'AND', column: 'site_id', location: @location
            )}")
            @rebuild = true if records['rec_count'].to_i < 1
          rescue StandardError => e
            Rails.logger.error "Error: #{e.message}"
            initialize_disaggregated
            rebuild_outcomes 'pepfar'
          end

          if @rebuild
            initialize_disaggregated
            rebuild_outcomes 'pepfar'
          end

        else
          start_date, end_date = generate_start_date_and_end_date(quarter)

          if @rebuild
            initialize_disaggregated
            art_service = CohortBuilder.new
            art_service.init_temporary_tables(@start_date, @end_date, @occupation)
            art_service.update_tb_status(end_date)
          end
        end

        tmp = get_age_groups(age_group, start_date, end_date)

        # A hack to get female that were pregnant / breastfeeding at the beginning of the reporting period + those are currently the same state
        if age_group == 'Pregnant'
          tmp_arr = []
          (tmp || []).each do |data|
            begin
              date_enrolled = data['date_enrolled'].to_date
            rescue StandardError => e
              Rails.logger.error "Error: #{e.message}"
              raise data.inspect
            end
            earliest_start_date = begin
              data['earliest_start_date']
            rescue StandardError
              date_enrolled
            end

            imstaus = data['initial_maternal_status']
            mstatus = data['mstatus']

            if (date_enrolled >= start_date && date_enrolled <= end_date) && imstaus == 'FP' && (date_enrolled == earliest_start_date)
              tmp_arr << data
            elsif mstatus == 'FP'
              tmp_arr << data
            end
          end

          tmp = tmp_arr
        end

        if age_group == 'Breastfeeding'
          tmp_arr = []
          (tmp || []).each do |data|
            begin
              date_enrolled = data['date_enrolled'].to_date
            rescue StandardError
              raise data.inspect
            end
            earliest_start_date = begin
              data['earliest_start_date']
            rescue StandardError
              date_enrolled
            end

            imstaus = data['initial_maternal_status']
            mstatus = data['mstatus']

            if (date_enrolled >= start_date && date_enrolled <= end_date) && imstaus == 'FBf' && (date_enrolled == earliest_start_date)
              tmp_arr << data
            elsif mstatus == 'FBf'
              tmp_arr << data
            end
          end

          tmp = tmp_arr
        end
        # ........................... Hack ends .......... Will clean up later

        on_art = []
        all_clients = []
        all_clients_outcomes = {}

        (tmp || []).each do |pat|
          patient_id = pat['patient_id'].to_i
          outcome = pat['outcome']

          on_art << patient_id if outcome == 'On antiretrovirals'
          all_clients << patient_id
          all_clients_outcomes[patient_id] = outcome
        end

        list = {}

        if all_clients.blank? && %w[Breastfeeding Pregnant].include?(age_group)
          list[age_group] = {}
          list[age_group]['F'] = {
            tx_new: [], tx_curr: [],
            tx_screened_for_tb: [],
            tx_given_ipt: []
          }
          return list
        elsif all_clients.blank?
          return {}
        end

        big_insert tmp, age_group if /year|month/i.match?(age_group)

        (tmp || []).each do |r|
          gender = r['gender']&.first || 'Unknown'
          patient_id = r['patient_id'].to_i
          tx_new, tx_curr, tx_given_ipt, tx_screened_for_tb = get_numbers(r, age_group, start_date, end_date,
                                                                          all_clients_outcomes)

          list[age_group] = {} if list[age_group].blank?

          if list[age_group][gender].blank?
            list[age_group][gender] = {
              tx_new: [], tx_curr: [],
              tx_screened_for_tb: [],
              tx_given_ipt: []
            }
          end

          list[age_group][gender][:tx_new] << r['patient_id'] if tx_new
          list[age_group][gender][:tx_curr] << r['patient_id'] if tx_curr
          list[age_group][gender][:tx_given_ipt] << r['patient_id'] if tx_given_ipt
          list[age_group][gender][:tx_screened_for_tb] << r['patient_id'] if tx_screened_for_tb

          date_enrolled = r['date_enrolled'].to_date

          if gender == 'F' && all_clients_outcomes[patient_id] == 'On antiretrovirals'
            insert_female_maternal_status(patient_id, age_group, end_date)
          elsif gender == 'F' && (date_enrolled >= start_date && date_enrolled <= end_date)
            insert_female_maternal_status(patient_id, age_group, end_date)
          end
        end

        list
      end

      def generate_start_date_and_end_date(quarter)
        return [@start_date, @end_date] if quarter == 'Custom'

        quarter, quarter_year = quarter.humanize.split

        quarter_start_dates = [
          "#{quarter_year}-01-01".to_date,
          "#{quarter_year}-04-01".to_date,
          "#{quarter_year}-07-01".to_date,
          "#{quarter_year}-10-01".to_date
        ]

        quarter_end_dates = [
          "#{quarter_year}-03-31".to_date,
          "#{quarter_year}-06-30".to_date,
          "#{quarter_year}-09-30".to_date,
          "#{quarter_year}-12-31".to_date
        ]

        current_quarter   = (quarter.match(/\d+/).to_s.to_i - 1)
        quarter_beginning = quarter_start_dates[current_quarter]
        quarter_ending    = quarter_end_dates[current_quarter]

        [quarter_beginning, quarter_ending]
      end

      def screened_for_tb(my_patient_id, age_group, start_date, end_date)
        data = ActiveRecord::Base.connection.select_one <<~SQL
          SELECT patient_screened_for_tb(#{my_patient_id},
            '#{start_date.to_date}', '#{end_date.to_date}', #{@location}) AS screened;
        SQL

        screened = data['screened'].to_i

        ActiveRecord::Base.connection.execute <<~SQL
          UPDATE temp_disaggregated SET screened_for_tb =  #{screened},
          age_group = '#{age_group}'
          WHERE patient_id = #{my_patient_id} AND site_id = #{@location};
        SQL

        screened
      end

      def given_ipt(my_patient_id, age_group, start_date, end_date)
        data = ActiveRecord::Base.connection.select_one <<~SQL
          SELECT patient_given_ipt(#{my_patient_id},
            '#{start_date.to_date}', '#{end_date.to_date}', #{@location}) AS given;
        SQL

        given = data['given'].to_i

        ActiveRecord::Base.connection.execute <<~SQL
          UPDATE temp_disaggregated SET given_ipt =  #{given} ,
          age_group = '#{age_group}'
          WHERE patient_id = #{my_patient_id} AND site_id = #{@location};
        SQL

        given
      end

      def get_numbers(data, age_group, start_date, end_date, outcomes)
        patient_id = data['patient_id'].to_i
        tx_new = false
        tx_curr = false
        tx_screened_for_tb = false
        tx_given_ipt = false
        outcome = outcomes[patient_id]

        begin
          date_enrolled = data['date_enrolled'].to_date
        rescue StandardError
          raise data.inspect
        end
        earliest_start_date = begin
          data['earliest_start_date'].to_date
        rescue StandardError
          nil
        end

        if date_enrolled >= start_date && date_enrolled <= end_date
          tx_new = true if !earliest_start_date.blank? && (date_enrolled == earliest_start_date)

          tx_curr = true if outcome == 'On antiretrovirals'
        elsif outcome == 'On antiretrovirals'
          tx_curr = true
        end

        if age_group == 'Pregnant'
          tx_new = false if data['initial_maternal_status'] != 'FP' && tx_new

          tx_curr = false if data['mstatus'] != 'FP'
        end

        if age_group == 'Breastfeeding'
          tx_new = false if data['initial_maternal_status'] != 'FBf' && tx_new

          tx_curr = false if data['mstatus'] != 'FBf'
        end

        [tx_new, tx_curr, tx_given_ipt, tx_screened_for_tb]
      end

      def get_age_groups(age_group, _start_date, _end_date)
        if age_group != 'Pregnant' && age_group != 'FNP' && age_group != 'Not pregnant' && age_group != 'Breastfeeding'

          age_group_patients = ActiveRecord::Base.connection.select_all <<~SQL
            SELECT
              patient_id, `disaggregated_age_group`(date(birthdate), date('#{@end_date}')) AS age_group
            FROM temp_earliest_start_date e #{site_manager(operator: 'WHERE', column: 'e.site_id', location: @location)}
            GROUP BY e.patient_id
            HAVING #{@adapter == 'mysql2' ? 'age_group' : "`disaggregated_age_group`(date(birthdate), date('#{@end_date}'))"} = '#{age_group}';
          SQL
          age_group_patient_ids = [0]
          (age_group_patients || []).each do |patient|
            age_group_patient_ids << patient['patient_id'].to_i
          end

          results = ActiveRecord::Base.connection.select_all <<~SQL
            SELECT o.cum_outcome AS outcome, e.*
            FROM temp_earliest_start_date e
            LEFT JOIN temp_patient_outcomes o ON o.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
            WHERE date_enrolled <= '#{@end_date}' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
            AND e.patient_id IN(#{age_group_patient_ids.join(',')})
            GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 'o.cum_outcome, e.*'};
          SQL
        elsif age_group == 'Pregnant'
          create_mysql_female_maternal_status
          results = ActiveRecord::Base.connection.select_all <<~SQL
            SELECT
              e.*, maternal_status AS mstatus,
              t2.initial_maternal_status,
              t3.cum_outcome AS outcome
            FROM temp_earliest_start_date e
            INNER JOIN temp_disaggregated t2 ON t2.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't2.site_id', location: @location)}
            INNER JOIN temp_patient_outcomes t3 ON t3.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't3.site_id', location: @location)}
            WHERE maternal_status = 'FP' OR initial_maternal_status = 'FP' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
            GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 't3.cum_outcome, maternal_status, t2.initial_maternal_status, e.*'};
          SQL

        elsif age_group == 'Breastfeeding'
          create_mysql_female_maternal_status
          results = ActiveRecord::Base.connection.select_all <<~SQL
            SELECT
              e.*, maternal_status AS mstatus,
              initial_maternal_status,
              t3.cum_outcome AS outcome
            FROM temp_earliest_start_date e
            INNER JOIN temp_disaggregated t2 ON t2.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't2.site_id', location: @location)}
            INNER JOIN temp_patient_outcomes t3 ON t3.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't3.site_id', location: @location)}
            WHERE maternal_status = 'FBf' OR initial_maternal_status = 'FBf' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
            GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 't3.cum_outcome, maternal_status, initial_maternal_status, e.*'};
          SQL

        elsif age_group == 'FNP'
          create_mysql_female_maternal_status
          results = ActiveRecord::Base.connection.select_all <<~SQL
            SELECT
              e.*, maternal_status AS mstatus,
              initial_maternal_status,
              t3.cum_outcome AS outcome
            FROM temp_earliest_start_date e
            INNER JOIN temp_disaggregated t2 ON t2.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't2.site_id', location: @location)}
            INNER JOIN temp_patient_outcomes t3 ON t3.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't3.site_id', location: @location)}
            WHERE maternal_status = 'FNP' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
            GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 't3.cum_outcome, maternal_status, initial_maternal_status, e.*'};
          SQL

        end

        results
      end

      def create_mysql_female_maternal_status
        ActiveRecord::Base.connection.execute <<~SQL
          DROP FUNCTION IF EXISTS female_maternal_status;
        SQL

        ActiveRecord::Base.connection.execute <<~SQL
          CREATE FUNCTION female_maternal_status(my_patient_id int, end_datetime datetime, my_site_id int) RETURNS VARCHAR(20)
          DETERMINISTIC
          BEGIN

          DECLARE breastfeeding_date DATETIME;
          DECLARE pregnant_date DATETIME;
          DECLARE maternal_status VARCHAR(20);
          DECLARE obs_value_coded INT(11);


          SET @reason_for_starting = (SELECT concept_id FROM concept_name WHERE LOWER(name) = LOWER('Reason for ART eligibility') LIMIT 1);

          SET @pregnant_concepts := (SELECT GROUP_CONCAT(concept_id) FROM concept_name WHERE LOWER(name) IN (LOWER('Is patient pregnant?'), LOWER('Patient pregnant')));
          SET @breastfeeding_concept := (SELECT GROUP_CONCAT(concept_id) FROM concept_name WHERE LOWER(name) = 'Breastfeeding');

          SET pregnant_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id IN (@pregnant_concepts) AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND site_id = my_site_id);
          SET breastfeeding_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id IN (@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND site_id = my_site_id);

          IF pregnant_date IS NULL THEN
            SET pregnant_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id = @reason_for_starting AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND value_coded IN(1755) AND site_id = my_site_id);
          END IF;

          IF breastfeeding_date IS NULL THEN
            SET breastfeeding_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id = @reason_for_starting AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND value_coded IN(834,5632) AND site_id = my_site_id);
          END IF;

          IF pregnant_date IS NULL AND breastfeeding_date IS NULL THEN SET maternal_status = "FNP";
          ELSEIF pregnant_date IS NOT NULL AND breastfeeding_date IS NOT NULL THEN SET maternal_status = "Unknown";
          ELSEIF pregnant_date IS NULL AND breastfeeding_date IS NOT NULL THEN SET maternal_status = "Check BF";
          ELSEIF pregnant_date IS NOT NULL AND breastfeeding_date IS NULL THEN SET maternal_status = "Check FP";
          END IF;

          IF maternal_status = 'Unknown' THEN

            IF breastfeeding_date <= pregnant_date THEN
              SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@pregnant_concepts) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = pregnant_date AND site_id = my_site_id LIMIT 1);
              IF obs_value_coded = 1065 THEN SET maternal_status = 'FP';
              ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
              END IF;
            END IF;

            IF breastfeeding_date > pregnant_date THEN
              SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id LIMIT 1);
              IF obs_value_coded = 1065 THEN SET maternal_status = 'FBf';
              ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
              END IF;
            END IF;

            IF DATE(breastfeeding_date) = DATE(pregnant_date) AND maternal_status = 'FNP' THEN
              SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id LIMIT 1);
              IF obs_value_coded = 1065 THEN SET maternal_status = 'FBf';
              ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
              END IF;
            END IF;
          END IF;

          IF maternal_status = 'Check FP' THEN

            SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@pregnant_concepts) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = pregnant_date AND site_id = my_site_id LIMIT 1);
            IF obs_value_coded = 1065 THEN SET maternal_status = 'FP';
            ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
            END IF;

            IF obs_value_coded IS NULL THEN
              SET obs_value_coded = (SELECT GROUP_CONCAT(value_coded) FROM obs WHERE concept_id IN(7563) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = pregnant_date AND site_id = my_site_id);
              IF obs_value_coded IN(1755) THEN SET maternal_status = 'FP';
              END IF;
            END IF;

            IF maternal_status = 'Check FP' THEN SET maternal_status = 'FNP';
            END IF;
          END IF;

          IF maternal_status = 'Check BF' THEN

            SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id LIMIT 1);
            IF obs_value_coded = 1065 THEN SET maternal_status = 'FBf';
            ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
            END IF;

            IF obs_value_coded IS NULL THEN
              SET obs_value_coded = (SELECT GROUP_CONCAT(value_coded) FROM obs WHERE concept_id IN(7563) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id);
              IF obs_value_coded IN(834,5632) THEN SET maternal_status = 'FBf';
              END IF;
            END IF;

            IF maternal_status = 'Check BF' THEN SET maternal_status = 'FNP';
            END IF;
          END IF;



          RETURN maternal_status;
          END;
        SQL
      end

      def rebuild_outcomes(report_type)
        MalawiHivProgramReports::Moh::CohortBuilder.new(outcomes_definition: report_type, location: @location).init_temporary_tables(@start_date,
                                                                                                                                     @end_date, @occupation)
      end

      def insert_female_maternal_status(patient_id, age_group, end_date)
        encounter_types = []
        encounter_types << encounter_type('HIV CLINIC CONSULTATION').encounter_type_id
        encounter_types << encounter_type('HIV STAGING').encounter_type_id

        pregnant_concepts = []
        pregnant_concepts << concept_name('Is patient pregnant?').concept_id
        pregnant_concepts << concept_name('patient pregnant').concept_id

        results = ActiveRecord::Base.connection.select_all <<~SQL
          SELECT person_id, obs.value_coded value_coded
          FROM obs obs
          INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
          WHERE obs.person_id = #{patient_id}
          AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
          AND obs.concept_id IN(#{pregnant_concepts.join(',')})
          AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
          AND DATE(obs.obs_datetime) = (SELECT MAX(DATE(o.obs_datetime)) FROM obs o
                        INNER JOIN encounter e ON e.encounter_id = o.encounter_id
                        AND e.program_id = 1 AND e.voided = 0 #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
                        WHERE o.concept_id IN(#{pregnant_concepts.join(',')})
                        AND o.voided = 0 AND o.person_id = obs.person_id #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
                        AND o.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}')
          GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
          HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
          ORDER BY MAX(obs.obs_datetime) DESC
        SQL

        female_maternal_status = results.blank? ? 'FNP' : 'FP'

        if female_maternal_status == 'FNP'

          breastfeeding_concepts = []
          breastfeeding_concepts <<  concept_name('Breast feeding?').concept_id
          breastfeeding_concepts <<  concept_name('Breast feeding').concept_id
          breastfeeding_concepts <<  concept_name('Breastfeeding').concept_id

          results2 = ActiveRecord::Base.connection.select_all <<~SQL
            SELECT person_id, obs.value_coded value_coded
            FROM obs obs
            INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
            WHERE obs.person_id =#{patient_id}
            AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
            AND obs.concept_id IN(#{breastfeeding_concepts.join(',')})
            AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
            AND DATE(obs.obs_datetime) = (SELECT MAX(DATE(o.obs_datetime)) FROM obs o
                          INNER JOIN encounter e ON e.encounter_id = o.encounter_id
                          AND e.program_id = 1 AND e.voided = 0 #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
                          WHERE o.concept_id IN(#{breastfeeding_concepts.join(',')}) AND o.voided = 0
                          AND o.person_id = obs.person_id #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
                          AND o.obs_datetime <='#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}')
            GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
            HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
            ORDER BY MAX(obs.obs_datetime) DESC
          SQL

          female_maternal_status = results2.blank? ? 'FNP' : 'FBf'
        end

        results = ActiveRecord::Base.connection.select_all <<~SQL
          SELECT person_id, obs.value_coded value_coded
          FROM obs obs
          INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
          WHERE obs.person_id = #{patient_id}
          AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
          AND obs.concept_id IN(#{pregnant_concepts.join(',')})
          AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
          AND DATE(obs.obs_datetime) = (SELECT DATE(es.earliest_start_date) FROM temp_earliest_start_date es
                                        WHERE es.patient_id = obs.person_id #{site_manager(operator: 'AND', column: 'es.site_id', location: @location)})
          GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
          HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
          ORDER BY MAX(obs.obs_datetime) DESC
        SQL

        initial_female_maternal_status = results.blank? ? 'FNP' : 'FP'

        if initial_female_maternal_status == 'FNP'

          breastfeeding_concepts = []
          breastfeeding_concepts <<  concept_name('Breast feeding?').concept_id
          breastfeeding_concepts <<  concept_name('Breast feeding').concept_id
          breastfeeding_concepts <<  concept_name('Breastfeeding').concept_id

          results2 = ActiveRecord::Base.connection.select_all <<~SQL
            SELECT person_id, obs.value_coded value_coded
            FROM obs obs
            INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
            WHERE obs.person_id =#{patient_id}
            AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
            AND obs.concept_id IN(#{breastfeeding_concepts.join(',')})
            AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
            AND DATE(obs.obs_datetime) = (SELECT DATE(es.earliest_start_date) FROM temp_earliest_start_date es
                                          WHERE es.patient_id = obs.person_id #{site_manager(operator: 'AND', column: 'es.site_id', location: @location)})
            GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
            HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
            ORDER BY MAX(obs.obs_datetime) DESC
          SQL

          initial_female_maternal_status = results2.blank? ? 'FNP' : 'FBf'
        end

        ActiveRecord::Base.connection.execute <<~SQL
          UPDATE temp_disaggregated SET maternal_status =  '#{female_maternal_status}',
            initial_maternal_status = '#{initial_female_maternal_status}',
             age_group = '#{age_group}' WHERE patient_id = #{patient_id} AND site_id = #{@location};
        SQL
      end

      def big_insert(data, age_group)
        insert_array = []
        (data || []).each do |r|
          insert_array << "(#{r['patient_id']}, '#{age_group}', #{r['site_id']})"
        end

        return if insert_array.blank?

        ActiveRecord::Base.connection.execute <<~SQL
          INSERT INTO temp_disaggregated (patient_id, age_group, site_id)
          VALUES #{insert_array.join(',')};
        SQL
      end
    end
    # rubocop:enable Metrics/ClassLength
  end
end