ALTER TABLE oil_suppliers
  ADD COLUMN IF NOT EXISTS master_supplier_id BIGINT NULL AFTER id,
  ADD COLUMN IF NOT EXISTS supplier_code VARCHAR(100) NULL AFTER master_supplier_id,
  ADD COLUMN IF NOT EXISTS tax_id VARCHAR(60) NULL AFTER name,
  ADD COLUMN IF NOT EXISTS address VARCHAR(255) NULL AFTER phone,
  ADD COLUMN IF NOT EXISTS city VARCHAR(120) NULL AFTER address,
  ADD COLUMN IF NOT EXISTS website VARCHAR(255) NULL AFTER city;

CREATE INDEX IF NOT EXISTS idx_oil_suppliers_master_supplier ON oil_suppliers(master_supplier_id);

UPDATE oil_suppliers os
JOIN md_suppliers ms ON UPPER(TRIM(ms.name))=UPPER(TRIM(os.name))
SET os.master_supplier_id=ms.id,
    os.supplier_code=COALESCE(NULLIF(os.supplier_code,''),ms.code),
    os.tax_id=COALESCE(NULLIF(os.tax_id,''),ms.tax_id),
    os.contact_name=COALESCE(NULLIF(os.contact_name,''),ms.contact_name),
    os.email=COALESCE(NULLIF(os.email,''),ms.email),
    os.phone=COALESCE(NULLIF(os.phone,''),ms.phone),
    os.address=COALESCE(NULLIF(os.address,''),ms.address),
    os.city=COALESCE(NULLIF(os.city,''),ms.city),
    os.website=COALESCE(NULLIF(os.website,''),ms.website)
WHERE os.master_supplier_id IS NULL;