-- Driver login and tenant isolation hardening
-- Safe to run manually if you are not using Sequelize sync alter.

ALTER TABLE drivers
  ADD COLUMN IF NOT EXISTS password_hash VARCHAR(255) NULL AFTER email,
  ADD COLUMN IF NOT EXISTS last_login_at DATETIME NULL AFTER status;

ALTER TABLE drivers
  DROP COLUMN IF EXISTS assigned_zone;

-- MySQL requires checking existing duplicate data before adding unique indexes in older deployments.
-- If duplicate driver_code or tenant phone exists, clean duplicates first, then run these indexes.
CREATE UNIQUE INDEX uq_drv_code ON drivers (driver_code);
CREATE UNIQUE INDEX uq_drv_tenant_phone ON drivers (tenant_id, phone);
CREATE INDEX idx_drv_tenant_status_del ON drivers (tenant_id, status, is_delete);
CREATE INDEX idx_drv_tenant_user_del ON drivers (tenant_id, user_id, is_delete);
