# frozen_string_literal: true

module EdiApp
  class A1piOutRepo < BaseRepo
    def a1pi_rows(govt_inspection_sheet_id)
      query = <<~SQL
        SELECT
        ps.production_run_id AS batch_number
        ,ps.carton_quantity AS cartons
        ,commodities.code AS commodity
        ,govt_inspection_sheets.consignment_note_number AS consignment_number
        , load_containers.actual_payload + load_containers.tare_weight AS container_gross_mass
        ,load_containers.container_code AS container_number
        , SUBSTR(container_stack_types.stack_type_code, 1, 1) AS container_size
        , SUBSTR(container_stack_types.stack_type_code, 1, 1) AS container_type
        -- dispatch_datetime --- ???
        ,destination_cities.city_name AS dispatch_destination
        ,fn_party_role_name(loads.exporter_party_role_id) AS exporter_code
        ,farms.farm_code
        ,grades.grade_code AS grade
        ,COALESCE(p.govt_reinspection_at, p.govt_first_inspection_at)::date AS inspection_date
        ,p.intake_created_at AS intake_datetime
        , depots.depot_code AS intake_location
        ,inventory_codes.inventory_code
        ,marks.mark_code AS mark
        ,ps.nett_weight AS net_mass  -- Pallet????
        ,orchards.orchard_code AS orchard
        ,standard_pack_codes.standard_pack_code AS pack
        ,packhouses.plant_resource_code AS packhouse_code
        ,COALESCE(p.gross_weight, load_containers.actual_payload / (( SELECT count(*) AS count
                   FROM pallets
                  WHERE pallets.load_id = loads.id))::numeric) AS pallet_gross_mass
        ,ps.pallet_number
        ,(ps.carton_quantity::numeric / p.carton_quantity::numeric)::numeric(8,2) AS pallet_quantity
        ,pallet_stack_types.stack_type_code AS pallet_stack
        ,pod_ports.port_code AS port_of_destination
        ,pol_ports.port_code AS port_of_origin
        ,ps.product_chars AS product_characteristic
        ,load_containers.container_seal_code AS seal_number
        ,ps.pallet_sequence_number AS sequence_number
        ,fn_edi_size_count(standard_pack_codes.use_size_ref_for_edi, commodities.use_size_ref_for_edi, fruit_size_references.edi_out_code, fruit_size_references.size_reference, fruit_actual_counts_for_packs.actual_count_for_pack) AS size_count
        -- stock_pool -- NULL
        , loads.shipped_at AS stuff_datetime
        -- stuff_location_code -- not known
        , load_containers.tare_weight AS tare_weight
        ,destination_countries.country_name AS target_country
        ,target_markets.target_market_name AS target_market
        ,destination_regions.destination_region_name AS target_region
        -- transaction_datetime -- time.now
        ,marketing_varieties.marketing_variety_code AS variety_code
        -- weight_verification_method -- SAMSA accreditaion - in PO... use same... AppConst::SOLAS_VERIFICATION_METHOD

        FROM govt_inspection_pallets
        JOIN pallets p ON p.id = govt_inspection_pallets.pallet_id
             JOIN pallet_sequences ps ON p.id = ps.pallet_id
             LEFT JOIN pallets repacked_from_pallets ON repacked_from_pallets.id = ps.repacked_from_pallet_id
             LEFT JOIN plant_resources plt_packhouses ON plt_packhouses.id = p.plt_packhouse_resource_id
             LEFT JOIN plant_resources plt_lines ON plt_lines.id = p.plt_line_resource_id
             LEFT JOIN plant_resources packhouses ON packhouses.id = ps.packhouse_resource_id
             LEFT JOIN plant_resources lines ON lines.id = ps.production_line_id
             LEFT JOIN plant_resources palletizing_bays ON palletizing_bays.id = p.palletizing_bay_resource_id
             JOIN locations ON locations.id = p.location_id
             JOIN farms ON farms.id = ps.farm_id
             LEFT JOIN farm_groups ON farms.farm_group_id = farm_groups.id
             JOIN production_regions ON production_regions.id = farms.pdn_region_id
             JOIN pucs ON pucs.id = ps.puc_id
             JOIN orchards ON orchards.id = ps.orchard_id
             JOIN cultivar_groups ON cultivar_groups.id = ps.cultivar_group_id
             LEFT JOIN cultivars ON cultivars.id = ps.cultivar_id
             LEFT JOIN commodities ON commodities.id = cultivar_groups.commodity_id
             JOIN marketing_varieties ON marketing_varieties.id = ps.marketing_variety_id
             JOIN marks ON marks.id = ps.mark_id
             LEFT JOIN pm_marks ON pm_marks.id = ps.pm_mark_id
             JOIN inventory_codes ON inventory_codes.id = ps.inventory_code_id
             JOIN target_market_groups ON target_market_groups.id = ps.packed_tm_group_id
             LEFT JOIN target_markets ON target_markets.id = ps.target_market_id
             JOIN grades ON grades.id = ps.grade_id
             LEFT JOIN customer_varieties ON customer_varieties.id = ps.customer_variety_id
             LEFT JOIN marketing_varieties cvv ON cvv.id = customer_varieties.variety_as_customer_variety_id
             LEFT JOIN std_fruit_size_counts ON std_fruit_size_counts.id = ps.std_fruit_size_count_id
             LEFT JOIN fruit_size_references ON fruit_size_references.id = ps.fruit_size_reference_id
             LEFT JOIN fruit_actual_counts_for_packs ON fruit_actual_counts_for_packs.id = ps.fruit_actual_counts_for_pack_id
             JOIN basic_pack_codes ON basic_pack_codes.id = ps.basic_pack_code_id
             JOIN standard_pack_codes ON standard_pack_codes.id = ps.standard_pack_code_id
             LEFT JOIN pm_boms ON pm_boms.id = ps.pm_bom_id
             LEFT JOIN pm_subtypes ON pm_subtypes.id = ps.pm_subtype_id
             LEFT JOIN pm_types ON pm_types.id = ps.pm_type_id
             JOIN seasons ON seasons.id = ps.season_id
             JOIN cartons_per_pallet ON cartons_per_pallet.id = ps.cartons_per_pallet_id
             LEFT JOIN pm_products ON pm_products.id = p.fruit_sticker_pm_product_id
             LEFT JOIN pm_products pm_products_2 ON pm_products_2.id = p.fruit_sticker_pm_product_2_id
             LEFT JOIN pallet_formats ON pallet_formats.id = p.pallet_format_id
             LEFT JOIN pallet_bases ON pallet_bases.id = pallet_formats.pallet_base_id
             LEFT JOIN pallet_stack_types ON pallet_stack_types.id = pallet_formats.pallet_stack_type_id
             LEFT JOIN pallet_verification_failure_reasons ON pallet_verification_failure_reasons.id = ps.pallet_verification_failure_reason_id
             LEFT JOIN loads ON loads.id = p.load_id
             LEFT JOIN load_voyages ON loads.id = load_voyages.load_id
             LEFT JOIN voyage_ports pol_voyage_ports ON pol_voyage_ports.id = loads.pol_voyage_port_id
             LEFT JOIN voyage_ports pod_voyage_ports ON pod_voyage_ports.id = loads.pod_voyage_port_id
             LEFT JOIN voyages ON voyages.id = pol_voyage_ports.voyage_id
             LEFT JOIN vessels ON vessels.id = voyages.vessel_id
             LEFT JOIN load_containers ON load_containers.load_id = loads.id
             LEFT JOIN load_vehicles ON load_vehicles.load_id = loads.id
             LEFT JOIN ports pol_ports ON pol_ports.id = pol_voyage_ports.port_id
             LEFT JOIN ports pod_ports ON pod_ports.id = pod_voyage_ports.port_id
             LEFT JOIN destination_cities ON destination_cities.id = loads.final_destination_id
             LEFT JOIN destination_countries ON destination_countries.id = destination_cities.destination_country_id
             LEFT JOIN destination_regions ON destination_regions.id = destination_countries.destination_region_id
             LEFT JOIN cargo_temperatures ON cargo_temperatures.id = load_containers.cargo_temperature_id
             LEFT JOIN govt_inspection_pallets ON govt_inspection_pallets.id = p.last_govt_inspection_pallet_id
             LEFT JOIN govt_inspection_sheets ON govt_inspection_sheets.id = govt_inspection_pallets.govt_inspection_sheet_id
             LEFT JOIN destination_countries inspected_dest_country ON inspected_dest_country.id = govt_inspection_sheets.destination_country_id
             LEFT JOIN edi_in_transactions ON edi_in_transactions.id = p.edi_in_transaction_id
             LEFT JOIN depots ON depots.id = loads.depot_id
             LEFT JOIN ( SELECT sq.id,
                    COALESCE(btrim(array_agg(orchard_test_types.test_type_code)::text), ''::text) <> ''::text AS failed_otmc,
                    array_agg(orchard_test_types.test_type_code) AS failed_otmc_results
                   FROM pallet_sequences sq
                     JOIN orchard_test_types ON orchard_test_types.id = ANY (sq.failed_otmc_results)
                  GROUP BY sq.id) otmc ON otmc.id = ps.id
             LEFT JOIN ( SELECT pallet_sequences.pallet_id,
                    string_agg(DISTINCT palletizers.identifier, ', '::text) AS palletizer_identifier,
                    string_agg(DISTINCT concat(palletizer_contract_workers.first_name, '_', palletizer_contract_workers.surname), ', '::text) AS palletizer_contract_worker,
                    string_agg(DISTINCT palletizer_contract_workers.personnel_number, ', '::text) AS palletizer_personnel_number
                   FROM cartons
                     LEFT JOIN personnel_identifiers palletizers ON palletizers.id = cartons.palletizer_identifier_id
                     LEFT JOIN pallet_sequences ON pallet_sequences.id = cartons.pallet_sequence_id
                     LEFT JOIN contract_workers palletizer_contract_workers ON palletizer_contract_workers.personnel_identifier_id = cartons.palletizer_identifier_id
                  GROUP BY pallet_sequences.pallet_id) palletizer_details ON ps.pallet_id = palletizer_details.pallet_id
             LEFT JOIN standard_product_weights ON standard_product_weights.standard_pack_id = standard_pack_codes.id AND standard_product_weights.commodity_id = commodities.id
             LEFT JOIN standard_product_weights standard_product_weights_for_commodity ON standard_product_weights_for_commodity.commodity_id = commodities.id AND standard_product_weights_for_commodity.is_standard_carton
             LEFT JOIN pucs marketing_pucs ON marketing_pucs.id = ps.marketing_puc_id
             LEFT JOIN registered_orchards ON registered_orchards.id = ps.marketing_orchard_id
             LEFT JOIN rmt_classes ON rmt_classes.id = ps.rmt_class_id
             LEFT JOIN pm_products tu_pm_products ON tu_pm_products.id = ps.tu_labour_product_id
             LEFT JOIN pm_products ru_pm_products ON ru_pm_products.id = ps.ru_labour_product_id
             LEFT JOIN rmt_container_material_owners ON rmt_container_material_owners.id = p.rmt_container_material_owner_id
             LEFT JOIN rmt_container_material_types ON rmt_container_material_types.id = rmt_container_material_owners.rmt_container_material_type_id
             LEFT JOIN colour_percentages ON colour_percentages.id = ps.colour_percentage_id
             LEFT JOIN treatments actual_cold_treatments ON actual_cold_treatments.id = ps.actual_cold_treatment_id
             LEFT JOIN treatments actual_ripeness_treatments ON actual_ripeness_treatments.id = ps.actual_ripeness_treatment_id
             LEFT JOIN rmt_codes ON rmt_codes.id = ps.rmt_code_id
             LEFT OUTER JOIN container_stack_types ON container_stack_types.id = load_containers.stack_type_id
             WHERE govt_inspection_sheet_id = ?
             ORDER BY ps.pallet_number, ps.pallet_sequence_number
      SQL
      DB[query, govt_inspection_sheet_id].all
    end

    def log_a1pi_success(file_name, record_id)
      # DB[:loads].where(id: record_id).update(edi_file_name: file_name)
      log_status(:loads, record_id, 'A1PI SENT', user_name: 'System', comment: file_name)
    end

    def log_a1pi_fail(record_id, message)
      log_status(:loads, record_id, 'A1PI SEND FAILURE', user_name: 'System', comment: message)
    end
  end
end
