-- Critical indexes for the MIN report query
create index if not exists idx_min_header_company_status on
   min_header (
      company_id,
      status_id,
      id
   desc );

create index if not exists idx_min_line_items_header_id on
   min_line_items (
      min_header_id
   );

-- CRITICAL: Indexes for UPPER(zain_tag) joins
create index if not exists idx_min_line_items_zain_upper on
   min_line_items ( upper(zain_tag) );

create index if not exists idx_mir_line_items_zain_upper on
   mir_line_items ( upper(zain_tag) );

create index if not exists idx_mrn_line_items_zain_upper on
   mrn_line_items ( upper(zain_tag) );

create index if not exists idx_stocks_zain_upper on
   stocks ( upper(zain_tag) );

-- Foreign key indexes (if missing)
create index if not exists idx_min_line_items_item_id on
   min_line_items (
      item_id
   );
create index if not exists idx_min_line_items_warehouse on
   min_line_items (
      warehouse_id
   );
create index if not exists idx_min_line_items_bin on
   min_line_items (
      bin_id
   );
create index if not exists idx_min_line_items_uom on
   min_line_items (
      uom_id
   );
create index if not exists idx_min_line_items_site_origin on
   min_line_items (
      site_id_origin
   );

create index if not exists idx_min_header_project on
   min_header (
      project_id
   );
create index if not exists idx_min_header_mir_header on
   min_header (
      mir_header_id
   );

create index if not exists idx_items_department on
   items (
      department_id
   );
create index if not exists idx_items_item_type on
   items (
      item_type_id
   );
create index if not exists idx_items_manufacturer on
   items (
      manufacturer_id
   );
create index if not exists idx_items_team on
   items (
      team_id
   );

create index if not exists idx_stocks_item_condition on
   stocks (
      item_condition_id
   );

create index if not exists idx_mir_header_vendor on
   mir_header (
      vendor_id
   );
create index if not exists idx_mir_header_region on
   mir_header (
      region_id
   );

create index if not exists idx_mir_line_items_site_destn on
   mir_line_items (
      site_id_destn
   );

create index if not exists idx_item_uoms_composite on
   item_uoms (
      item_id,
      uom_id
   );