-- dbt Silver Cleansed Manifest Orders Enriched with Master Facilities
SELECT 
  o.order_id,
  o.manifest_code,
  o.order_date,
  o.generator_company,
  o.waste_category,
  o.volume_ton,
  o.tariff_per_ton,
  o.transport_fee,
  ROUND((o.volume_ton * o.tariff_per_ton) + o.transport_fee, 2) AS total_order_amount,
  o.order_status,
  f.facility_id,
  f.facility_name,
  f.region_zone,
  f.facility_type,
  f.compliance_rating,
  f.facility_status,
  CASE 
    WHEN f.facility_status = 'ACTIVE' AND o.volume_ton <= f.max_daily_capacity_ton THEN 'COMPLIANT'
    WHEN f.facility_status != 'ACTIVE' THEN 'UNLICENSED_FACILITY_WARNING'
    ELSE 'CAPACITY_EXCEEDED_WARNING'
  END AS environmental_compliance_status
FROM {{ ref('bronze_waste_orders') }} o
LEFT JOIN {{ ref('bronze_ref_facilities') }} f
  ON o.facility_id = f.facility_id
WHERE o.order_status != 'CANCELLED'