LCFS - Fix ETL Script to Retrieve Correct GHGenius Carbon Intensity Value

[AlexZorkin]

Review with Product Owner:
Before implementation, confirm with the product owner whether GHGenius intensity values should remain as a separate field or be merged into a fuel_type_other value. This discussion is critical due to recent talks about potential changes in how these values are handled.

Description
The current ETL script for migrating Schedule B records to LCFS uses crsbr.intensity for records determined via GHGenius, which is insufficient. Instead, the script must obtain the correct carbon intensity from TFRS Schedule D data using the schedule_d_sheet_index value. This approach should mimic the logic in the TFRS codebase.

Task

• Identify Target Section:
  Locate the ETL code block handling intensity determination for records where dt.the_type equals 'GHGenius'.

  WHEN dt.the_type = 'GHGenius' THEN crsbr.intensity -- TODO fix intensity to extract from Schedule-D sheets

• Reference TFRS Code:
  Use these TFRS code references as guidance:
  • ScheduleDSheet (from TFRS):

    @property
    def carbon_intensity(self):
        out = self.outputs.all()
        total = 0
        for record in out:
            total += record.intensity
        return round(total / 1000, 2)

  • ScheduleBRecord.effective_carbon_intensity (from TFRS):

    @property
    def effective_carbon_intensity(self):
        if self.provision_of_the_act is None:
            return None
        t = self.provision_of_the_act.determination_type.the_type
        if t == 'GHGenius':
            i = self.schedule_d_sheet_index
            sheets = self.schedule.compliance_report.schedule_d.sheets.all()
            return sheets[i].carbon_intensity
        # ... additional logic for other determination types ...

• Implementation Steps:
  1. Join or Query Schedule D Data:
     Modify the ETL script to join or query the compliance_report_schedule_d_sheet table using the compliance_report_id and the schedule_d_sheet_index from the Schedule B record.
  2. Extract Carbon Intensity:
     Retrieve the carbon intensity value from the corresponding Schedule D sheet, following the logic in ScheduleDSheet.carbon_intensity.
  3. Replace Placeholder:
     Replace the current placeholder (crsbr.intensity) with the retrieved carbon intensity value for GHGenius records.
  4. Logging and Error Handling:
     Implement detailed logging for the retrieved value and handle errors gracefully if the join or retrieval fails.

Acceptance Criteria

• The ETL script correctly extracts the carbon intensity from the TFRS Schedule D sheet based on the schedule_d_sheet_index.
• For records where dt.the_type is 'GHGenius', the LCFS fuel_supply.intensity field is populated with the value derived from the corresponding Schedule D sheet.
• Other determination types (Alternative, Fuel Code, Default Carbon Intensity) remain unaffected.
• Detailed logging is implemented, and any errors in data retrieval are properly handled.
• Final changes are documented and reviewed with the product owner prior to deployment.

This ticket ensures that the ETL process correctly maps and retrieves the carbon intensity value for GHGenius records by leveraging the established logic in the TFRS codebase.