alter table if exists final_result_products
  add column if not exists match_bucket text not null default 'matched';

create index if not exists idx_final_result_products_match_bucket
  on final_result_products(match_bucket);

with product_rows as (
  select
    fr.batch_id,
    fr.material_id,
    fr.task_id,
    nullif(product.value->>'index', '')::smallint as rank,
    product.value as product
  from final_results fr
  cross join lateral jsonb_array_elements(
    case
      when jsonb_typeof(fr.products) = 'array' then fr.products
      else '[]'::jsonb
    end
  ) as product(value)
),
matched_products as (
  select *
  from product_rows
  where rank is not null
)
update final_result_products fp
set
  score = coalesce(nullif(matched_products.product->>'score', '')::real, fp.score),
  is_match = coalesce(nullif(matched_products.product->>'is_match', '')::boolean, fp.is_match),
  match_bucket = coalesce(
    nullif(matched_products.product->>'match_bucket', ''),
    nullif(fp.match_bucket, ''),
    case
      when nullif(matched_products.product->>'is_match', '')::boolean is true then 'matched'
      when fp.is_placeholder then 'placeholder'
      else 'not_match'
    end
  ),
  reason = coalesce(matched_products.product->>'reason', nullif(fp.reason, ''), ''),
  has_phone_contact = coalesce(
    nullif(matched_products.product->>'has_phone_contact', '')::boolean,
    fp.has_phone_contact,
    false
  ),
  qualification_status = coalesce(
    nullif(matched_products.product->>'qualification_status', ''),
    nullif(fp.qualification_status, ''),
    'qualified'
  ),
  contact_collection_reason = coalesce(
    matched_products.product->>'contact_collection_reason',
    nullif(fp.contact_collection_reason, ''),
    ''
  )
from matched_products
where fp.batch_id = matched_products.batch_id
  and fp.material_id = matched_products.material_id
  and fp.rank = matched_products.rank
  and coalesce(fp.task_id, '') = coalesce(matched_products.task_id, '');
